Evaluating SQL Server Database Mirroring as a Disaster Recovery Solution

By:   |   Comments (5)   |   Related: 1 | 2 | 3 | 4 | > Database Mirroring

Your company is considering a disaster recovery solution for SQL Server 2005 and 2008 instances. You have been asked to analyze the various options and report in detail which method is the best for your company.  Over the course of the analysis you are also asked to find out if the DR solution can also be used for reporting purposes.  Let's see how SQL Server Database Mirroring resolves these needs in a simple question and answer format.

Q1. With SQL Server 2005 or 2008 Database Mirroring does the database mirrored database need to be in full recovery mode?

Yes, that is correct.  The DBA's should backup the transaction logs frequently. On a related note, if the mirrored databases are not in a synchronized state, the transaction log file will continue to grow even if transaction log backups are being issued.  This is because the unsent portion of the transaction log is increasing.  You can monitor  the database mirror state using the sp_dbmmonitorresults stored procedure in the MSDB database.

Q2. Which is better SQL Server log shipping or Database Mirroring?

In many respects which technology is better is dependent on the needs and some personal preferences to resolve the need.  Here are some thoughts from my perspective:

  • Log Shipping
    • Log shipping is a time tested method and it works just fine.
    • I personally like log shipping, because it has what I consider a strong fault-tolerant feature.  If the secondary server is not available for few hours or a few days, the log shipping primary server is not affected at all.  The processes continue to backup the transaction log.  As soon as the secondary server is available, the processes can start copying/restoring the log files and sync with the primary server.  If the log shipping is out of sync, by restoring the full database backup and all transaction logs, log shipping catches up without the need to do make any configuration changes on the principal server.
    • Log shipping is a warm standby solution. Failover is manual and it may take quite sometime, unless appropriate scripts are in place for a quick failover.
  • Database Mirroring
    • In the case of database mirroring, as I explained in question 1, you have to monitor the log file size if the mirror server is not available.
    • Database Mirroring also adds some extra load on the principal server, since it is the job of the principal to keep the database mirror synchronized.
    • If database mirroring is removed for some reason, the whole process of configuring mirroring has to be started from scratch.
    • Mirroring offers a real-time or a hot standby solution.
    • Failover is very quick and can be automated without any user intervention.

Q3. How do you re-synchronize the principal database and mirror database after an interruption in database mirroring?

If Database Mirroring goes to into suspended state, you can always click the "resume" button in Management Studio and try to synchronize the databases. In some cases if we have to remove mirroring, there is no easy way to re-synchronize the databases. We have to start from the scratch with a full backup followed by the transaction log backups from the principal, copy them to the mirror server, restore them with the no recovery option and then reconfigure database mirroring.

Q4. Can servers in different domain participate in database mirroring?

Yes. Certificate based security needs to be configured so the principal and mirror server can trust each other.

Q5a. Can database snapshots be created on a database mirror database ?

Yes.  Mirroring is typically used for a disaster recovery solution and/or for a reporting solution. Since the mirror database is in "norecovery" or "loading" state, user connections are not possible. However, we can create database snapshots on the mirrored database and use them for reporting.  Also, note that you can create the snapshot only if the mirror database is in "synchronized" state. So, make sure the the mirror database is synchronized before you drop and try to recreate a new one.

FROM sys.database_mirroring
WHERE db_name(database_id) = 'MyMirrorDB'
AND mirroring_state_desc = 'SYNCHRONIZED'

Q5b. Can I drop a database snapshot created from the mirror database ?

Yes it is possible.  You can drop the snapshot and create a new one so the reporting application can report on the most recent data on a regular interval, but dropping a snapshot has some consequences because the plan cache is cleared as well.  This causes recompilation of stored procedures which is a performance degradation.  Check out this site for additional information - http://blogs.msdn.com/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx.

Q6. Are there any other options maintaining a reporting instance other than Database Mirroring and Log Shipping?

Yes.  Replication can be used as an alternate for migrating data to another SQL Server instance for reporting purposes.  Here are some additional tips on replication.

Q7. What are some options to monitor database mirroring?

Here is a good document on monitoring database mirroring - http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx that outlines how to setup events and alerts on performance and database state changes.

Next steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ranga Narasimhan Ranga Narasimhan has been in IT for over 10 years working on Siebel, Visual Basic, Crystal Reports and SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, January 22, 2014 - 3:46:27 AM - James MacDonald Back To Top (28170)

Hi Ranga

Have you configured Siebel ODBC to use a mirror server?  I've tried it but when I failover the primary database to the mirror Siebel can no longer connect even though the ODBC has the connectivity information for the mirror server.  I'm using Siebel 8.1 and SQL Server 2008 R2



Monday, October 7, 2013 - 5:02:43 PM - A S Back To Top (27067)

can i rollback changes in DR mirrored database after failing over (for test) using database snapshot, and restart mirroring without full backup/restore from primary?

Monday, February 4, 2013 - 4:20:39 PM - Ranga Back To Top (21906)


It depends on the latency/bandwidth of your network. How long the mirror database takes to sync 1-2 minutes or in 1-2 seconds ? Also, you might want to do a POC and see if the latency is acceptable. Here is a link for pre-requisities:



Monday, February 4, 2013 - 2:34:03 PM - Sreenivas Reddy Back To Top (21904)


Hi ,

Can we Create DB mirroring between 2 different datacenters? Suppose my primary DC is in NYC and SECONDARY DC is in CA.

If so, what is the pre-requisites?. Please suggest.






Tuesday, December 16, 2008 - 12:30:16 PM - jerryhung Back To Top (2413)

 I want to stress that Database Snapshot is ONLY available in SQL Server 2005/2008 ENTERPRISE edition 

so if you are on Standard, don't even consider Mirroring for Reporting Purpose. I'd use Log Shipping on top of Mirroring to another report server with STANDBY/read-only mode

get free sql tips
agree to terms