Database mirroring and log shipping. Which is better?

  • [アーティクル]

You may ask yourself and others this question all the time. I have heard the question a lot. The quick answer...it depends. I think both are great, however, your situation may make one more attractive than the other.

 

Here's a quick list of the main benefits of each technology as well as some limitations:

 

  • Database Mirroring benefits:
    • Instantaneous synchronization of changes
    • Easy setup and maintenance
    • Automated failover (in High Availability Mode)
    • Easy to perform failover
    • Fast failover. (if no transactions are queued)
    • Applications can be mirroring aware using failover partner parameter in connection string
  • Database Mirroring limitations:
    • Limited number of databases can be mirrored per SQL instance. (however, this number is higher than the 10 you see plastered everywhere, but really depends on your hardware)
    • Mirrored database cannot be queried, backed up, or basically touched in any way (snapshot can be taken however)
    • Only supports one to one server pairings
    • Cannot mirror a database more than once
    • Synchronous mirroring is intolerant to latency and bandwidth constraints (Asynchronous mirroring has no problems with latency or bandwidth)
  • Log Shipping benefits:
    • Very flexible - Independent jobs govern backup/copy/restore behavior
    • Delay of restore job can protect from database corruption
    • Log shipped database can be queried. (in standby mode)
    • Log shipping allows multiple standby databases
    • Perhaps more tolerant to latency than sychronous mirroring.
    • Doesn't affect performance of primary server
    • Easy setup
  • Log Shipping limitations:
    • No automated failover
    • Cannot synchronize faster than once a minute
    • Failover is more complicated than mirroring
    • Harder to view status of log shipping

Comments

  • Anonymous
    July 28, 2007
    Michael may ask himself whether mirroring or log shipping is better... Database mirroring and log shipping.

  • Anonymous
    July 28, 2007
    Michael may ask himself whether mirroring or log shipping is better... Database mirroring and log shipping

  • Anonymous
    July 29, 2007
    Michael Watson posted a short blog on Database mirroring and log shipping. Which is better? but this

  • Anonymous
    July 29, 2007
    In a response to my blog entry Database Mirroring and Log Shipping . Which is better? The mysterious

  • Anonymous
    July 29, 2007
    In a response to my blog entry Database Mirroring and Log Shipping . Which is better? The mysterious

  • Anonymous
    July 29, 2007
    Last week at TechReady I attended Mike Watson's and James Petrosky's talk on HA and DR scenarios. Good

  • Anonymous
    July 29, 2007
    Last week at TechReady I attended Mike Watson's and James Petrosky's talk on HA and DR scenarios

  • Anonymous
    August 20, 2007
    Using database mirroring with Office SharePoint Server [Note: this whitepaper was recently updated, July,

  • Anonymous
    June 28, 2008
    Hi, We have a situation where we have 3 seperate branches and the WAN links can drop at any time due to weather etc. As a result our SharePoint implementation needs to cater for these occurences. Our current thought is to have one main production SharePoint site with the two remote branches having some sort of backup site which is a duplicate of the main site. There are products out there that can handle this arrangement however they are in the 10's of thousands of dollars per location which is outside our available budget. I was wondering if you have heard of a way to do this using Log shipping etc. I was thinking of each branch having a local SharePoint site which are all configured to point to the same  database server. Then if at all possible have a script at each branch which when run changes the local SharePoint site to point to the local database. The limitation being the resynchronization after the link between branches is restored. Any ideas would be appreciated.

  • Anonymous
    June 28, 2008
    Hi, We have a situation where we have 3 seperate branches and the WAN links can drop at any time due to weather etc. As a result our SharePoint implementation needs to cater for these occurences. Our current thought is to have one main production SharePoint site with the two remote branches having some sort of backup site which is a duplicate of the main site. There are products out there that can handle this arrangement however they are in the 10's of thousands of dollars per location which is outside our available budget. I was wondering if you have heard of a way to do this using Log shipping etc. I was thinking of each branch having a local SharePoint site which are all configured to point to the same  database server. Then if at all possible have a script at each branch which when run changes the local SharePoint site to point to the local database. The limitation being the resynchronization after the link between branches is restored. Any ideas would be appreciated.

  • Anonymous
    August 04, 2008
    The comment has been removed

  • Anonymous
    April 08, 2009
    H1 { FONT-SIZE: medium } Many thanks to everyone who attended the MOSS 2007 and SQL 2008 "Better Together"

  • Anonymous
    April 15, 2009
    (Cross-posted from Vantage Point: Bob German's Weblog ) H1 { FONT-SIZE: medium } Recently Rich Crane

  • Anonymous
    February 10, 2012
    The other issue is that with log shipping you need 2 sql licenses, with mirror you do not.

  • Anonymous
    February 12, 2014
    Its not harder to view status of logshipping.I dont agree with that point and this does not comes under disfference.I dont find this blog  a quality one which msdn blogs are known for

  • Anonymous
    December 20, 2015
    Here is another helpful post for the same: mostafaelmasry.com/.../sql-server-transaction-log-shipping-vs-mirroring-a-comprehensive-guide