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 (16)

  1. Reed Me says:

    Michael may ask himself whether mirroring or log shipping is better… Database mirroring and log shipping.

  2. Michael may ask himself whether mirroring or log shipping is better… Database mirroring and log shipping

  3. Michael Watson posted a short blog on Database mirroring and log shipping. Which is better? but this

  4. In a response to my blog entry Database Mirroring and Log Shipping . Which is better? The mysterious

  5. In a response to my blog entry Database Mirroring and Log Shipping . Which is better? The mysterious

  6. Last week at TechReady I attended Mike Watson’s and James Petrosky’s talk on HA and DR scenarios. Good

  7. Last week at TechReady I attended Mike Watson's and James Petrosky's talk on HA and DR scenarios

  8. Using database mirroring with Office SharePoint Server [Note: this whitepaper was recently updated, July,

  9. Pobblebonk says:

    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.

  10. Pobblebonk says:

    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.

  11. MSDNArchive says:

    There’s no MSFT solution that can do this. The problem is that there is no way to merge the two divergent datasets using out of the box functionality. This is what we call multi-master or bi-directional replication. Syntergy is the only product I know of that is capable of this. http://www.syntergy.com/sharepoint%5Cproducts/replication/

  12. H1 { FONT-SIZE: medium } Many thanks to everyone who attended the MOSS 2007 and SQL 2008 "Better Together"

  13. (Cross-posted from Vantage Point: Bob German’s Weblog ) H1 { FONT-SIZE: medium } Recently Rich Crane

  14. allan says:

    The other issue is that with log shipping you need 2 sql licenses, with mirror you do not.

  15. Shanky_621 says:

    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