Difference between Log Shipping and Database Mirroring

This is self study notes kind of post.

Log shipping

• Primary server, secondary server and monitor server are the components in log shipping set up.
• Monitor server is an optional.
• Log shipping is a manual failover process.
• There will not be an automatic application connection redirection. It has to be redirected manually.
• Log shipping will have multiple secondary databases for the synchronization.
• There will be data transfer latency.
• In log shipping, secondary database can be used for reporting solution.
• Both committed and uncommitted transactions are transferred to the secondary database.
• Log shipping supports both bulk logged recovery model and full recovery model.

With Log Shipping:

• Data Transfer: T-Logs are backed up and transferred to secondary server
• Transactional Consistency: All committed and un-committed are transferred
• Server Limitation: Can be applied to multiple stand-by servers
• Failover: Manual
• Failover Duration: Can take more than 30 minutes
• Role Change: Role change is manual
• Client Re-direction: Manual changes required

 

Database Mirroring

• Principal server, mirror server, and witness server are the components involve in database mirroring set up.
• Witness server is an optional but it is a must for setting up automatic failover since witness is a watchdog instance to check if principal server is working.
• Database mirroring is an automatic failover process.
• Application connection can be redirected automatically with proper configuration.
• Database mirroring will not have multiple database destinations for mirroring the principal database. It will have one mirror database synchronizes with principal database.
• There will not be data transfer latency.
• In database mirroring, mirror database cannot be used for reporting solution. If need comes, database snapshot should be created to set up for the reporting solution.
• Only committed transactions are transferred to the mirror database.
• Mirroring supports only Full Recovery model.

With Database Mirroring:

• Data Transfer: Individual T-Log records are transferred using TCP endpoints
• Transactional Consistency: Only committed transactions are transferred
• Server Limitation: Can be applied to only one mirror server
• Failover: Automatic
• Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds
• Role Change: Role change is fully automatic
• Client Re-direction: Fully automatic as it uses .NET 2.0

 

Please note that database mirroring feature will be discontinued with SQL Server 2014 and Microsoft is recommending AlwaysOn feature instead of Log shipping or database mirroring for that version onwards.