Minimize downtime with DB Mirroring
At one high volume OLTP project we are involved in, the customer likes to use DB Mirroring to minimize downtime for planned failovers. This usually involves patching either Windows or SQL Server.
The interesting part of this strategy is that they switch to synchronous mirroring just before doing a failover. They would normally like to run with synchronous mirroring but their volume is too high during the day to keep up. So they run asynchronous mirroring until just before they want to switch. They plan these failovers for a lower volume time, usually around midnight.
Here are the steps to take to apply patches:
1. Patch the mirror machine first. If this means that SQL Server needs to be stopped for a while to apply patches, this company is ok with the risk of running without a mirror for a short period of time.
2. Start the mirror machine again and let asynchronous mirroring resume and catch up.
3. Switch to synchronous mirroring, and let it get to steady state.
4. Fail over to the mirror machine.
5. Switch to asynchronous mirroring
6. Patch the principal, which may need a restart.
7. When the original machine is back on line, asynchronous mirroring will start again and eventually catch up. (sometimes 15-30 minutes with this application, which is I/O limited on the mirror side)
8. Switch to synchronous mirroring, and let it get to steady state.
9. Fail back to the original principal machine.
10. Switch back to asynchronous mirroring.
So far we’ve been fortunate that the outages are between 10 and 15 seconds. We know that sometimes mirroring failovers can take longer.
If you have a witness server, you can upgrade that to SQL Server 2008 first, before you upgrade either the principle or the mirror.
In addition to doing Windows patches and SQL Server cumulative updates, this technique was also used to upgrade from SQL 2005 to SQL 2008. We upgraded the mirror first, then upgraded the principal in the same steps as above. One requirement for this scenario is that your SQL Server 2005 instance must be running Build 3215 (SP2 Cumulative Update 5) or better, or else you will not be able to establish a mirroring partnership with SQL Server 2008. The only worry was that there was no way to go back once we failed over to SQL 2008 since mirroring will not work with a SQL 2008 source and a SQL 2005 destination. We thought about creating an additional replicated database from the mirror, but decided to take the risk since we had the luxury of practicing the upgrade on a good set of test servers that were under a simulated production load. If you want to see the detail of how to replicate from a mirrored database, see this link: https://sqlcat.com/whitepapers/archive/2008/09/02/sql-server-replication-providing-high-availability-using-database-mirroring.aspx.
Another recommendation that makes database mirroring much easier to setup with a high volume application is to make sure that both sides of the mirror are running on Windows Server 2008. When you are “preparing the mirror” to set up a mirroring partnership, you have to take a full backup of the principal database, copy the backup file(s) to the server where the mirror database will run, and then restore the database with no recovery on that instance. Depending on your database size, your workload, and your infrastructure, you will probably have to do several transaction log backups of the principal database, which will also have to be copied to the mirror and restored with no recovery. You are essentially racing to catch up with the principal database.
The SMB 2.0 and other network stack improvements in Windows Server 2008 make network file copies between servers that are both running Windows Server 2008 nearly an order magnitude faster than on Windows Server 2003 R2. If you are also running the Enterprise Edition of SQL Server 2008, you should use native backup compression for your full database backup and your transaction log backups. This will make the backups complete more quickly, make the backup files much smaller (which makes them easier to copy to the other server), and will make the restores finish more quickly. You should also make sure that the SQL Server service account on the mirror has the “Perform Volume Maintenance Tasks” right in Windows for that server, so you can use Windows Instant File Initialization. This will make your full restore complete much more quickly. Using Windows Server 2008 and SQL Server 2008 together can cut many hours off the time required to establish a mirror on a high volume OLTP system.
To get more information on SQL Server database mirroring, check these links (Some of these were written for SQL 2005 but still apply to SQL 2008):
· Database Mirroring and Log Shipping Working Together: https://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
· Implementing Application Failover with Database Mirroring: https://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
· Database Mirroring Best Practices and Performance Considerations: https://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
· Database Mirroring FAQ: https://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx
· Performance Boost for Database Mirroring: https://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Performance-boost-for-Database-Mirroring.aspx
· Troubleshooting Database Mirroring Deployment: https://msdn.microsoft.com/en-us/library/ms189127.aspx
· How to Avoid Orphaned Users With Database Mirroring:
https://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!494.entry
Hope this helps,
Kevin Cox - SQL CAT; Glenn Berry – SQL MVP and DBA.