Get all the angles on SQL Server database mirroring

Database mirroring is a high-availability option in SQL Server that’s easy to set up, but for seamless failover, you’ll need to become familiar with the three recommended modes for SQL Server database mirroring, as potential pitfalls are numerous.

Database mirroring is one of several high-availability options in SQL Server. Although the concept is simple, and it’s fairly easy to set up, there are a few things to consider before implementing database mirroring. After all, you want any potential failover to the mirrored server to be seamless. In this article I will provide guidelines and recommendations based on my experiences with SQL Server database mirroring in a production environment.

There are three operation modes for SQL Server database mirroring: high safety with a witness, high safety without a witness, and high performance without a witness. While a fourth combination, high performance with a witness, is theoretically possible, it is discouraged because it could make the primary database unavailable if the witness server fails.

The first recommendation I can give you is become familiar with how these three modes work and know what happens in each mode if one of the servers in the mix fails. Now let’s look at the details of each.

High-safety mode is also known as synchronous mode. Since each database operation is written to the transaction log on both servers, you’ll never lose data even in case of a failover. The mirror may occasionally lag behind, but eventually it catches up after it writes out all transactions from the log to the data file.

If you run in high-safety mode without a witness, the primary database will be available, but you won’t get the automatic failover. If the mirror server becomes unavailable, mirroring suspends, but the primary database continues working. The transactions will not get purged from the log, however, and if you don’t pay attention, you may run out space on the primary. If the primary fails, the mirror will be available, but you’ll need to implement a manual failover to bring the databases online and redirect your application.

Because you’ll need to do a manual intervention in either case, it’s important to set up notification in case something goes wrong with your database mirroring session or any server involved in mirroring. 

For more on SQL Server database mirroring

Grasp the basics of SQL Server database mirroring

Learn how to set up database mirroring in SQL Server 2005

Understand the role of the witness in SQL Server database mirroring

If you are run in high-safety mode with a witness, you’ll get automatic application failover, so your applications should continue working as long as they are configured to connect to the mirror server. The behavior on the database side changes based on which server you lose. If you lose the primary server, the mirror and the witness decide as a quorum that the mirror should take over and the databases go into failover.

You now have the same problem with transaction logs on the mirror not being truncated. But in this scenario, it is even more important to respond immediately. If the witness server subsequently stops working (while the primary is still down), the databases on the mirror become unavailable because the quorum with both the primary and the witness was lost. In this case, SQL Server makes the database unavailable because it doesn’t yet know whether the primary database is accepting updates; therefore, it needs to guarantee that updates at any time are only possible to one database.

So, the important thing for you to remember is this -- if you are running in high-safety mode with a witness and you lose the primary or mirror server, you need to very quickly fix the unavailable server or break database mirroring so that you don’t run out of space and the databases become unavailable even if the failover server is running OK.

Once you are familiar with these mirroring modes, set up your servers and create a set of documented failover procedures. Your plan should include all possible scenarios: loss of the primary server, loss of the mirror, loss of the witness, or loss of primary followed by a loss of witness and vice versa. In each case describe the expected database and application state and behavior and what procedure should be followed to make sure everything continues running. Then perform a series of failover tests and run through each scenario. Make sure you are prepared to respond quickly and appropriately; this will ensure your applications continue running and you don’t experience any of the side effects described in this article.

In case of a failover to mirror, be it manually or automatically, you’ll likely need to take additional steps to make the mirror fully functional such as enabling SQL Agent jobs on the mirror-turned-primary and enabling Windows-scheduled tasks, backups, etc. Remember, mirroring only fails over user databases; all the other objects in system databases as well as additional dependencies need to be set up on the mirror server. For more on performing a successful failover, read my tips for SQL Server database failover success.

One of the things you might encounter when you run in high-safety mode with a witness is that an automatic failover might occur if there is a short network blip or something similar. The reason is that by default a failover is initiated after the primary is unresponsive for 10 seconds. Consider modifying this value and increase it to, say, 30 seconds. Keep in mind though that whatever you increase that value by, it will take that much longer before the mirror takes over during a real failover. You can modify the timeout setting by running the following query on the primary server:



Figure 1
Figure 1

As I’ve mentioned, it is important to monitor your SQL Server database mirroring sessions. If you register the database in the Database Mirroring Monitor, you can set up warnings for four different thresholds, as shown in Figure 1.

Figure 2
Figure 2

Once you configure these threshold warnings, the Database Mirroring Monitor will show you warnings if any of these values exceed the threshold. If you want to get automatic notifications through SQL Agent, define a SQL Agent alert and specify the alert number. Figure 2 shows you how to configure an alert notifying you when the unsent log threshold is exceeded.

Note that you need to enter the error number for each threshold type. Here are the error numbers for the four threshold warnings:

Performance Threshold Error Number
Unsent log 32042
Unrestored log 32043
Oldest unsent transaction 32040
Mirror commit overhead 32044

About the author:
Roman is principal database architect at MyWebGrocer in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He regularly contributes SQL Server articles to Visual Studio Magazine, SQL Server Magazine and other technical publications and presents at user groups and conferences in the U.S. and Canada.

Dig Deeper on SQL Server High Availability, Scalability and Reliability