Evaluating SQL Server Database Mirroring as a Disaster Recovery Solution
Written By: Ranga Narasimhan -- 12/16/2008
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
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
- Mirroring is a great new feature originally introduced in SQL Server 2005 however it has limitations as well as pros and cons like any other technology solution.
- Understand your needs and then do the research to figure out which native or third party solution best meets the needs.
- Test the technology extensively and pay special attention to:
- How mirroring can be monitored
- How long it may take to re-sync the principal and mirror database
- How long the failover takes
- How much data will be lost on a failover
- Application changes needed to support database mirroring
- Check out these related tips:
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
