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
