Database Mirroring in SQL Server 2008 R2 and SQL Server 2008

Keeping the database available for customer use is one of a DBA’s primary concerns. Many technologies are available to assist DBAs with availability, both for single-server and multiple-server configurations. Database mirroring can be a great way to improve database availability because it’s fairly easy to deploy, administer, and maintain. In this article I explain the fundamentals of mirroring, including how to determine whether it’s the right choice for your environment and how to get a basic configuration up and running. Because SQL Server 2008 R2 doesn’t include any significant enhancements to database mirroring, this article applies to both SQL Server 2008 R2 and SQL Server 2008.

Related: Database Mirroring for Disaster Recovery

Overview

Database mirroring is one of SQL Server’s high-availability options. It’s the newest addition to SQL Server’s multiserver family of capabilities, first introduced in SQL Server 2005. As implied by its name, database mirroring lets you keep a remote copy of a single database up-to-date with changes from the primary copy of your database. The mirroring is done one database at a time rather than at the server level. Typically, this means it’s much easier to set up a mirrored configuration than using other high-availability technologies, such as failover clustering.

The disadvantage of mirroring working at the database level is that system databases, such as master, model, tempdb, and msdb, aren’t mirrored (and, in fact, can’t be mirrored). This means logins, SQL Server Agent jobs, and other security configurations aren’t identical between the two servers. The result is that a bit more administrative overhead is necessary to make sure customer connectivity works on both the servers involved in mirroring; however, you have more flexibility in the use of both systems.

Related: Stopping Database Mirroring

Database mirroring involves a principal server and a mirror server, as well as an optional witness server. The witness server doesn’t participate in mirroring per se—it just helps decide when an automatic failover can occur, under very specific circumstances. The principal and mirror roles for each database swap when a failover occurs. 

The two types of mirroring are synchronous and asynchronous. In synchronous mirroring, also called full transaction safety, a transaction on the principal database isn’t considered to be committed until a copy of the transaction log record for that transaction is physically copied over the network to the mirror server. Thus, you can guarantee no data loss in a synchronous configuration. In an asynchronous configuration, also referred to as having transactional safety turned off, the log records are sent to the mirror server but aren’t guaranteed to be in sync. Thus, some data loss can result if a failure occurs on the primary system before the committed transaction log records are copied to the mirror server.

Within synchronous mode, you can select either automatic or manual failover. In manual failover mode, the administrator can cause a failover to the mirror partner database with a simple T-SQL command or via the SQL Server Management Studio (SSMS) GUI. With automatic failover, you can still perform a manual failover, but if failure conditions are detected, then a failover to the mirror partner will happen without administrative intervention. Automatic mode requires a third server, the witness, be involved. A witness server doesn’t have a copy of the data, unlike the other two servers in a mirrored configuration. For an automatic failover to occur, the witness must agree with the mirror server that they're both unable to make a client connection to the database on the principal server. If they both agree that the principal database server is unavailable, the database on the mirror server is recovered and becomes available for client connections, thereby acting as the principal server.

Although synchronous mirroring requires SQL Server Standard Edition or better, a witness can run on any edition of SQL Server. However, if the witness server fails for some reason, both the primary server and the mirror server must be running. If one of them fails, the database will become unavailable until you manually intervene. Randomly selecting a noncritical server to serve as the witness for a database mirror configuration isn’t a good idea, because your availability is only as good as your witness server. 

Before we dig any further into mirroring, I want to stress a couple of configuration requirements. As I stated previously, SQL Server 2008 R2 Standard, Enterprise, or Datacenter is required for synchronous mirroring (high safety mode). Both the principal server and the mirror server must be running the same edition of SQL Server. SQL Server 2008 R2 Enterprise Edition or higher is necessary for asynchronous mirroring. The database you’re mirroring must be in full recovery mode and mustn’t be using the FILESTREAM feature.

Although your mirror server can act as a backup for your principal mirrored server, you should have the same hardware resources (memory, CPU, disk subsystem) for both servers. The mirror server requires roughly the same amount of space for the database as the principal server, and in the event of a failover the mirror server becomes the production machine. Service level agreements (SLAs) for this database might or might not allow for degraded performance after a failover—you must make that determination when you size the hardware. 

Design and Configuration

The first question to ask is whether database mirroring is a good fit for the availability goals you established for your database. Mirroring can be a good fit if you need only a single database and if your entire schema is contained in one database. But if your design includes parts of the logical schema in two or more physically separate databases on your server, mirroring probably isn’t the best solution. In that case, you’d want a solution that keeps multiple databases at a time highly available—failover clustering is the only such technology that’s built in to SQL Server.

If your entire logical schema is in a single database, the next thing to consider is whether you need synchronous or asynchronous mirroring. Can your business afford the possibility of data loss? If not, you should use synchronous database mirroring.

An additional consideration for synchronous mirroring is the network latency between the two mirrored servers. The more latency that exists between the two servers, the more the principal transaction server’s performance will be affected (because no commit is final until the log records are physically secured on the mirror server). Therefore, a high-speed network connection is essential between the principal and mirrored servers for synchronous mirroring to be a viable solution.

As a side note, database mirroring in asynchronous mode could be handy for secondary availability protection or for disaster recovery. If you have a local failover cluster (perhaps with some sort of SAN mirroring for the physical data files as well), then you might consider asynchronous log shipping to a remote site for additional protection or for disaster recovery purposes. Some risk of data loss still exists—but in an extreme scenario in which an entire data center becomes unavailable, a small amount of data loss might be acceptable. 

Security Considerations

You need to keep several security considerations in mind when configuring database mirroring. The first thing to consider is that under the hood, Service Broker endpoints are used to establish database mirroring connectivity. Each of these endpoints has a TCP port assigned, and you must ensure that your network and firewall allow communication to occur on those ports. If you create the endpoints yourself (using the CREATE ENDPOINT T-SQL statement), select the TCP listener port. If you create the endpoints via SSMS, an unused TCP port is suggested for use. You must also specify whether or not you want all mirroring transmissions to be encrypted. You’ll trade off security for more CPU to perform the encrypt/decrypt operations on either side of the mirroring session. SSMS encrypts the connection by default.

Another security consideration is that the servers must be able to establish a secure communication channel. The best solution is if both servers are in the same domain or in domains that have a trust relationship. If so, you can simply use Windows authentication to move the data. If your servers aren’t in a valid domain relationship and you still want to mirror the database to the remote server, you must use certificates to establish the mirror session’s logins and encryption. For more information about how to use certificates in this scenario, see “Setting Up Database Mirroring Using Certificates (Transact-SQL).” Keep in mind that if your database clients are using Windows authentication, they won’t be able to connect to the mirror server, either. Note also that if you use a witness server, the witness server must be able to make secure connections to both the principal server and the mirror server. 

Logins, linked servers, etc., are stored in the master database. Because database mirroring mirrors only a single database, none of the contents of the master database automatically appear on the mirror server. When you set up mirroring, you must ensure that the relevant logins for your database clients also exist on your mirror server. Any additional SQL Server components that are used outside your database must also be manually copied to the mirror server (e.g., linked servers, CLR objects, jobs, alerts, database mail configurations). Many ISV applications don’t have any of these dependencies and are therefore great candidates for simple mirroring. But if you do take advantage of these features in your applications, be sure you account for them when you establish database mirroring.

Initializing Mirroring

After you have the security configuration in place for your mirroring session, you must move a copy of your database from the principal server to the mirror server. The easiest way to do so is to back up the principal database, then use the NORECOVERY option to restore that database on the mirror server. If you perform any log backups after the database backup, you must also restore those transaction log backups on the mirror server. When you’re done, the mirrored server’s database should be recovered to as close to the current time as possible so that the log records still exist on the principal server’s copy of the database.

To illustrate database mirroring, let’s step through the SSMS GUI and establish a mirrored database for SQL Server. I’ll use the AdventureWorks sample database in my example; you can download this database at http://msftdbprodsamples.codeplex.com/releases. My example is for a synchronous mirroring session with a witness (i.e., automatic failover allowed). The AdventureWorks database is in simple recovery mode by default, so my first step is to switch it to full recovery mode, as Figure 1 shows.

Next, you need to create a full backup of the AdventureWorks database and copy that backup to the server you want to use for your mirror. This process might take a while for a real database, which is likely to be significantly larger than the AdventureWorks database. Remember to use the NORECOVERY option during restore, as Figure 2 shows (for an SSMS restore).

After the restore is complete, select the database, right-click, and navigate to the Mirror option, as Figure 3 shows. The Database Properties dialog box will launch on the mirroring tab. Click Configure Security to start the Configure Database Mirroring Security Wizard. Click Next, specify that you want to configure a witness server, and click Next again. The dialog box in Figure 4 launches, to let you configure the principal server instance.

Notice that you can change the port number, but SSMS attempts to select a port that isn’t in use. You can also change the endpoint name. Encryption is enabled by default. Click Next to launch the dialog box that lets you select your mirror server instance (which looks very similar to Figure 4). You can configure the port, encryption, and endpoint name. As long as you’re using two different servers for the configuration, you can use the same listener port and endpoint name on both sides of the connection. Click Next to launch the dialog box that lets you configure the witness server. Click Next again to specify the service accounts for each instance of SQL Server, as Figure 5 shows. If the service accounts aren’t the same on each instance in your mirror configuration, you can specify for SSMS to add logins to each instance on each server. However, using the same service account for each instance of SQL Server is recommended to keep the security simple.

Click Next to obtain a summary dialog box of the actions to be performed on your behalf, then click Finish to run the appropriate T-SQL commands. The T-SQL code that’s generated will look similar to the code in Listing 1.

After the T-SQL code runs, the Database Properties dialog box launches, as Figure 6 shows. To begin mirroring, click Start Mirroring. If you made a configuration error, you’ll get an error dialog box such as the one in Figure 7. Examples of configuration errors include making a typographical mistake when entering the server configuration, neglecting to specify in the wizard that you aren’t using the same domain account on every instance of SQL Server, and forgetting to open the appropriate TCP port in Windows Firewall. After you grant firewall exception on all three servers, click OK, then click Start Mirroring. After the endpoints are created (Listing 1), logins are created and connect privileges are granted if they don’t already exist for each instance (Listing 2).

To verify that mirroring is working properly, start the Database Mirroring Monitor. Right-click AdventureWorks, then select Tasks, Launch Database Mirroring Monitor. You’ll see a window similar to Figure 8, which shows that the mirror is in sync. If something were amiss, you could view the problem and begin troubleshooting steps from this monitor. You can also select information from the sys.database_mirroring catalog view to query the state of mirroring.

Client View

For the most part, client configuration for mirroring is the same as for connecting to any other SQL Server instance. You need to ensure that you’re using the Database attribute to switch to the correct mirrored database (in our case, AdventureWorks). You add a new parameter in your connection string, depending upon your API (see Table 1). To connect using managed code, modify your connection string and set the server to something like Server=RWCLUSTN1; Failover Partner=RWCLUSTN2; Database=AdventureWorks; Network=dbmssocn. In the event of a database failover, the client application automatically switches to the mirror partner. If you can’t modify the client’s connection strings and you still want a high-availability solution, you’ll probably have to resort to failover clustering.

Table 1: Database Mirroring Client Connectivity Keywords by Technology

(Table information is from “Making the Initial Connection to a Database Mirroring Session.”)

Database Mirroring Is Easy

Once you have the proper security and server configuration in place, database mirroring is fairly straightforward. You have to bounce around a bit in terms of running commands on each of the three servers (in the case of a highly safe synchronous mirroring solution with a witness), and you have to jump back and forth with the initial database backup and restore. But after you accomplish those tasks, it’s easy to get a mirrored database off the ground.

For more information and details about database mirroring, see “Database Mirroring.” For an advanced discussion of database mirroring best practices, see “Database Mirroring Best Practices and Performance Considerations.”