A Practical Approach to Database Mirroring

When you're looking for a high-availability solution, you might find that although third-party providers offer good solutions and complete packages, their products can get pretty expensive. Once you evaluate the costs, you might decide that implementing your own database mirroring solution is a no-brainer. However, implementing and supporting a successful mirroring solution takes some thought and planning. Sure, you can slap together a mirroring solution with little effort, but if you don't have a handle on every aspect of your environment, you could be in for a bumpy ride. You must consider such aspects as network speed, security, maintenance, upgrades, and failover. (For information about how to decide whether mirroring is right for you, see the Web sidebar "Should You Choose Database Mirroring?", linked at the top of this article page.) If you don't include all the necessary aspects in your implementation, you could bring your production environment down in a matter of minutes.

So, let's look at how to set up and support mirroring as an alternative to an expensive third-party high-availability solution. In this example, we might not need the entire server to fail over; instead, we'll assume we're working in an environment in which we need only a single database or two to be mirrored. What you'll find is that although mirroring is an excellent high-availability technology for individual user databases, it's far from a complete disaster-recovery technology. For one thing, mirroring doesn't protect system databases, whereas third-party products typically do. So although you wouldn't use mirroring to fail over an entire server, it can be a good choice for protecting important data and it doesn't involve the complexity of replication.

Configuring Your Network

Getting mirroring going is easy enough, but the process can reveal previously unknown network problems. Therefore, it's always best to get your mirroring setup running on a non-production system before taking the plunge with your important data.

First, you need to make sure you have the proper Fully Qualified Domain Name (FQDN) for both your primary and secondary servers. You can verify the FQDN by connecting to the server console either directly or through Windows 2000 Server Terminal Services. In Terminal Services, go to Start, Run, and type cmd. Then, type ipconfig/all. You should see a screen like the one that Figure 1 shows.

Notice in Figure 1 that the second line in the result set is Primary Dns Suffix. This setting is what SQL Server is looking for when setting up mirroring. You'll have to work out any DNS issues before you can set up mirroring, so this setting is a good place to start. If the primary DNS suffix isn't what you're expecting to see, the easiest way to fix the problem is to add the proper DNS suffix in the connection's IP properties. You can see how to add the DNS suffix on the DNS tab of the Advanced TCP/IP Settings dialog box in Figure 2.

Why does SQL Server require you to use the server's FQDN instead of the NETBIOS (domain name) or even the IP address? This security requirement lets SQL Server be sure it is talking to the desired server for Secure Sockets Layer (SSL) and the mirroring network validation checks. (You must provide a canonical name that can be compared to a self definition of the machine.) You also have to address any network problems before you have a successful mirroring setup.

Getting Set Up for Mirroring

Now, you can begin to set up mirroring. Follow these basic steps:

This list is oversimplified, so let's walk through the details of the setup that you'll encounter in the mirroring pane of the database properties window and the Database Mirroring Security Wizard. Note that you must perform the backups and restores yourself.

That's a good place to start—so begin by performing a full backup and a log backup, then restore them both on the secondary server. You must restore your backups with the same names that they have on the primary database, but the restored databases don't have to be in the same location on the secondary server. In addition, it's OK to run the log backup right after the full backup; you don't have to leave any time between them.

At this point, you might wonder why you even need the log backup? If you've already restored the full backup with no recovery, why take a log backup? Shouldn't mirroring just be able to pick up where the full backup left off? The answer is yes and no. Although you could use only the full backup, SQL Server requires the first log backup to be restored to ensure that no bulk logged pages are in the database. The log backup includes any bulk logged pages and clears the flag that indicates the existence of bulk logged pages. If you took a log backup after the one applied to the mirror, that backup could include bulk logged pages that were generated and backed up but left no traces in the log. In this case, the bulk logged data wouldn't make it to the mirror because it's not in the log stream and no bulk logged bits are set.

Once the backups are restored on the secondary server, we can start the wizard and establish the mirroring session. Rightclick the primary database and go to Tasks, Mirror or go to Properties, then select the Mirroring tab. Click Configure Security to start the Configure Database Mirroring Security Wizard that Figure 3 shows. In the Include Witness Server dialog box, you'll almost always want to configure a witness. In addition to being needed for failover, the witness prevents what's known as a split-brain scenario, in which the link between the two servers gets broken for some reason and each one thinks the other is down. In such a case, the secondary database will become the primary database, and when the link is re-established, both databases think they're primary. Not pretty. Having a witness prevents this error. You can use any edition of SQL Server as your witness, so if you don't already have a free server, you can install SQL Server Express somewhere reliable and you'll be fine.

The principal server instance defaults to the server you started the wizard from, so be sure to start the wizard from the principal. You also have to start the wizard from the right database. The wizard doesn't have a place to pick the database to mirror; it assumes you started it from the database you want. You can pick your own port and endpoint name in the Principal Server Instance dialog box that Figure 4 shows, but it's best to accept the defaults. The only reason to pick something different is if the current settings are already in use or are restricted. So for example, if you already had an endpoint named Mirroring, you'd have to choose another name. Or you might have to choose an alternative port if the listener port is in use by another service or firewall restrictions prevent you from using the default. If you don't have any of these circumstances, there's no reason to change the defaults unless you just want to.

Next, you choose the mirror instance in the Mirror Server Instance dialog box, which looks similar to the dialog box in Figure 4. Then, the wizard will force you to connect, and you'll get to make the same choices for the witness in the Witness Server Instance dialog box.

Now, configure the service accounts for each server. If you leave these blank, as Figure 5 shows, it's assumed they're all the same domain accounts or are non-domain accounts. I didn't need to configure service accounts for my scenario because I'm using the same account on all my servers.

Finally, the Success dialog box tells you if anything has gone wrong. After you close the box, the wizard asks you if you want to start mirroring, as you can see in Figure 6.
If you click Do Not Start Mirroring, you can start mirroring any time from the Mirroring pane of the database's Properties window. However, if you wait too long and the logs get out of synch, you'll have to apply another log backup and start the wizard from scratch.

Backups

Now that you have a working mirroring setup, you need to know how to manage it. Naturally, because the mirror is just a standby instance of the primary database, you don't have to back it up. In fact, you can't back it up because it isn't available for any kind of connection. So if you have automated routines that back up all databases on the server, you'll need to exclude the mirror or you'll get an error every time the job runs. Also, depending on how you have your backup job set up, it might not continue backing up any of the other databases. For example, if you have a stored procedure that cursors through sys-.databases, backing up each-database as it goes, you have two choices. First, you could call the backup statement directly from the stored procedure, in which case the backup will stop on the first error. So if the first database in the cursor is the mirrored instance, the procedure will fail and none of the other databases will get backed up. Your second choice is to build the backup statement,assign it to a variable, and execute the variable. This choice causes the backup statement to run outside the context of the stored procedure, and although that statement will produce an error on the mirrored instance, it will continue to move forward and back up the rest of the databases. In either case, you'll want to exclude the mirrored database from the cursor because it will always produce an error that you have to investigate.

Index Maintenance and Upgrades

You also have to understand the effect mirroring will have on your maintenance routines. If you're running mirroring in full safety mode (synchronous), all of your REINDEX and DEFRAG statements will be applied to both servers in real time. This requirement could easily increase your maintenance time by 100 percent or more. So if you have a strict maintenance window, you might want to consider changing your mirroring to asynchronous mode before you begin maintenance. Let's look at the choices you have for managing mirroring with your maintenance routines.

The same considerations come into play when you have schema changes. Especially when you're using mirroring, you must test schema changes before you implement them because some changes can cause massive log activity and you'll need to benchmark ahead of time. For example, if you add a column to a large table and fill it with default values, you could double your processing time and the upgrade time. For a more detailed discussion of database mirroring performance considerations, see the Microsoft article "Database Mirroring Best Practices and Performance Considerations" at http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx.

Automated Monitoring

You'll also want to monitor your mirroring setup to make sure your secondary server isn't getting too far behind if you're running in asynchronous mode (safety off). To do so, you can use the monitoring GUI that is included in SQL Server 2005 Management Services, Service Pack 1 (SSMS SP1). This GUI will give you real-time statistics about your mirroring setup. I'm not going to go into details about the SSMS GUI here because most DBAs don't have the time to monitor a mirroring scenario for very long. Instead, I recommend using the Perfmon counters. You can add these counters to existing monitoring solutions such as MOM, OpenView, or Tivoli and set alert thresholds, as you would with any counter. You'll notice a new object in Perfmon: SQL Server: Database Mirroring. In the object, you'll find several interesting counters that display statistics about how many bytes get pushed between the two servers. The two counters that will be most useful to you in benchmarking and diagnosing performance issues are the Log Send Queue and Redo Queue counters. The larger these numbers get, the further behind your mirror is getting, and you should look into the root cause of the latency.

The Bottom Line

Mirroring can be a powerful tool in your high-availability scenario if you pay attention to its limitations and benchmark and monitor it properly. Mirroring is easy to set up and provides zero data loss under the right conditions. And although it can complicate maintenance scenarios, it's easier to support than replication or clustering. Mirroring can also be an attractive alternative to the often-bloated costs of third-party applications. You can download the T-SQL scripts you need to set up mirroring and some common management tasks at http://www.sqlmag.com. Click Download the Code at InstantDoc ID 95293.