page contents

About the Post

Author Information

What is better to use Database Mirroring or Log Shipping for SQL Server?

Well, the answer to this question begins with every experienced DBA’s favorite answer… It depends… 🙂

A great place to start is with this MSDN article on Database Mirroring and Log Shipping (SQL Server). It is kind of like a questionnaire to determine what would be best and it explains this topic in fairly simple terms.

Before I begin, I do want to point out that you can do both mirroring and log shipping. Another point about mirroring is that the official recommendation by Microsoft is to only mirror a maximum of 10 databases per server because each mirroring session consumes 2 threads. Log shipping does not have this limitation. You can find more details/best practices with this KB Article and TechNet blog.

Database mirroring is a functionality in the SQL Server engine that will read from the transaction log and then copies those transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. 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, etc.

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. It also can provide you with a more flexible solution to use a reporting server instance with older data.

In my experience, database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages:

1. Provides backup files as part of the process
2. Multiple secondaries are supported
3. Possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error or data corruption

More information about both technologies is available in SQL Server Books Online in the topics “Log Shipping Overview” and “Overview of Database Mirroring“.

I have created a simple spreadsheet for both technologies to help you determine which one might be more suited for your needs:

Database Mirroring Chart:

 

Log Shipping Chart:

Tags: , , , ,

One Response to “What is better to use Database Mirroring or Log Shipping for SQL Server?”

  1. Excellent website. Plenty of helpful info here. I’m sending it to some pals ans also sharing in delicious. And of course, thank you in your sweat!

    April 28, 2013 at 1:08 PM
Copy Protected by Chetan's WP-Copyprotect.