Database mirroring and log shipping. Which is better?

  • Article

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