SQL Server – Database mirroring vs Log Shipping

SQL Server – Database mirroring vs Log Shipping

I would like to start this topic with very common question for database administrators.

Question: What is difference between the Database Mirroring and Log Shipping? Which is preferable solution?

Database Mirroring:

Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance.  Database mirroring can operate synchronously or asynchronously. Database mirroring supports only one mirror for each principal database. Database mirroring also supports automatic failover if the principal database becomes unavailable.  The mirror database is always offline in a recovering state, but you can create snapshots of the mirror database to provide read access for reporting purposes etc.

Log Shipping

Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s).  Log shipping supports an unlimited number of secondaries for each primary database.

I personally don’t think that anyone is preferable over another one because both have their pros and cons. All I can say it’s all depend upon your requirements. More information about both technologies is available in SQL Server – Book Online.