SQL Server Performance

Log shipping to create a reporting server ???

Discussion in 'SQL Server 2005 Log Shipping' started by DBADave, Feb 9, 2009.

  1. DBADave New Member

    We have transaction log backups running every 15 minutes on a SQL 2005 Enterprise Edition - 64bit server. We would like to implement some type of failover solution, ideally where the secondary server could be accessed for reporting purposes. Is it possible to setup log shipping to apply the t-logs every hour using STANDBY even though t-log backups run every 15 minutes on the primary server? I read the following in BOL.
    You can optionally specify a delay for when the transaction log backup is restored in the secondary database
    Is this how I would specifiy restores every x minutes or every x hours?
    If t-log backups are taken every 15 minutes and they are applied every 60 minutes, what hapens to the logs that have yet to be applied. Are they queued up on the primary server or the secondary server?
    If a reporting server is not an option would we be better off considering database mirroring?
    Thanks, Dave
  2. Elisabeth Redei New Member

    Hi Dave,
    Logshipping is a very simple feature; basically it consists of 3 jobs:
    1. Backup job on Primary (also writes information about last backed to the log_shipping_monitor_primary table)
    2. Copy transaction log job on Secondary (also writes information about last copied file to the log_shipping_monitor_secondary table)
    3. Restore transaction log job on Secondary (also writes information about last restored log file to the log_shipping_monitor_secondary table)
    ... and then you have Alerts that will fire if the jobs are not run successfully in the threshold you specify. So the copy and restore jobs will just take the file following the last file processed (so yes, it will sort of queue up).
    It works very well to have your Secondary as a reporting server if you can find a balance between
    - how up-to-date you want the data to be on your Secondary
    - how long the job to apply transaction logs takes
    So in your case, you need to find out how long it takes to restore 1 hour worth of transaction logs. If that takes 40 minutes then it is not going to work very well to use the secondary as a reporting server because it will only be available to clients for 20 minutes every hour.
    You can't use a Database mirror directly as a Reporting database but you can use it together with Database Snapshots.
    :)
    /Elisabeth
  3. DBADave New Member

    Thanks Elisabeth. We're looking at alternate high/semi-high availability options to a SAN cluster because we finally confirmed with EMC that our Clarion has a bad disk bottleneck which we, the DBA's, have been saying this since November. Now we are being told by EMC to throw more disk $$$$ at the problem in order to achieve better performance. Our test server, which is local attached storage SAS drives outperforms the same disk config. on our SAN. So if we find out adding disks doesn't yield sufficient improvement in performance we are going to scrap the SAN clustering idea in favor of local attached storage. This is leading to a crash course in database mirroring and other options. I want to also see if there is a third-party replication tool that will allow readonly access to a secondary server.
    Is there anything to be aware of with database mirroring? I believe system databases cannot be mirrored and I don't recall how you get people to reference the mirrored server in the event of a failover. Is it just a sp_dropserver, sp_addserver to rename it or would that not work?
    Thanks for you help
    Dave
  4. Elisabeth Redei New Member

    I feel your pain, Dave! Yes, there are a few things to be aware of with database mirroring (one of them you mentioned so logins and jobs needs attention) but I think you will find they are not too difficult to overcome. You can set up mirroring in 3 different ways depending on your requirements:
    1. High performance mode
    Asynchronous mirroring and the only way to switch roles is to force it and this mode allows for some data loss.
    2 and 3. High Safety mode
    Synchronous mirroring with or without automatic failover
    Newer clients (SNAC or ADO.net) are mirroring aware and will keep entries for the mirror (so it can do automatic re-direct) but down-level clients cannot so there would have to be some custom logic to handle the re-direct.
    You can start off here for a more information about how it works "Database Mirroring Best Practices and Performance Considerations ", http://technet.microsoft.com/en-us/library/cc917681.aspx.
    So basically your a looking for something that gives you some increase in availability and at the same time it should provide you with some scale-out functions for reporting activity? If you can figure out the points I mentioned for Log shipping that is a quite easy maintained option I think (althought it doesn't provide you with any automatic client redirect feature). As I mentioned, the database mirror is not accesible to clients so a combination with Database Snapshots are necessary if you want both HA and reporting.
    If I were you I would start off with what the requirements are for high availability (how much downtime is tolerable, how much data can be lost etc.) and once you have that settled, figure out what you need to do in order get reporting capabilities.
    After all, if the reporting doesn't work you can always revert to the production data (with some performance loss) but if your High availability scheme and Disaster Recovery plan don't work out it can become a lot uglier than that!
    /Elisabeth
  5. jcorbi New Member

    Excuse me.
    ¿How about transactional replication in order to create and maintain a reporting server?
    In my case, the latency between the two servers are critical.
    Thanks in advance
    Jesús.
  6. satya Moderator

    Dave
    I believe if you are looking for Disaster REcovery for entire Server instance then Clustering is only method, if its for Database then DB Mirroring is best to go or for few tables then replication.
    I see your requirement is DB based, so I suggest to go with DBMirroring which is an efficient want to manage. But on the other side you are looking to provide reporting from that standby server and if the budget allows you need to pay licensing if you are using MIRROR server for reporting or querying purpose.

Share This Page