Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Evaluating SQL Server Database Mirroring as a Disaster Recovery Solution


By:   |   Last Updated: 2008-12-16   |   Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Mirroring

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

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

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

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



Last Updated: 2008-12-16


next webcast button


next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

thanks

James


Monday, October 07, 2013 - 5:02:43 PM - A S Back To Top

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 04, 2013 - 4:20:39 PM - Ranga Back To Top

Srinivas,

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:

http://msdn.microsoft.com/en-us/library/ms190941.aspx

Ranga


Monday, February 04, 2013 - 2:34:03 PM - Sreenivas Reddy Back To Top

 

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.

 

regards

Sreenivas

 

 


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

 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


Learn more about SQL Server tools