Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Difference between log shipping and database mirroring

Compiled by Abi Chapagai (April 2010)

Implementation of High Availability depends in the need of the business. Each of the technologies has advantages and disadvantages and sometime the situation comes where there cannot be any choice between them. I have recently implemented log shipping and database mirroring and would like to address difference between them. Both of them are used are high availability solution for disaster recovery but they do have differences between them.

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.

 

DB 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.

Comments

Posted by hai21century on 15 July 2010

Very interesting and useful article. Thanks Abi

Posted by sermohan-1082835 on 19 June 2012

good tks

Posted by vineet.tech1982 on 15 December 2012

Nicely differentiated

Posted by Akkare on 30 January 2013

Only committed transactions are transferred to the mirror database.

Then what is undo phase in Mirror Failover ?

Posted by smartrambit on 22 May 2014

thanks.nice and simple explanation

Posted by nagesh969 18044 on 2 September 2014

Really a good post..thanks alot dear friend.

Leave a Comment

Please register or log in to leave a comment.