Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Database Mirroring

Last updated Mar 28, 2003.

Microsoft SQL Server includes several technologies that not only provide protection for your databases, but also allow you to move data from one location to another for other purposes. Database Mirroring is one of those technologies.

There are various levels of protection your applications need, and SQL Server provides a lot of options to help you develop your shop's level of recovery. All of these options come with varying degrees of complexity and cost. You should work with your management staff to help them understand what the risks your application environment faces, and what you can do about those risks. I’ve covered the process of disaster recovery planning in another tutorial, and in this one I'll spend a little time on a specific recovery mechanism that SQL Server gives you: Database Mirroring. I’ll cover an overview of the feature and a simple two-system test you can try out on your own (testing) servers.

Database Mirroring is a technology that was introduced in SQL Server 2005 (Service Pack 1) that transfers the data in the database log from one server to another.

Database Mirroring Background

Database Mirroring is used to transfer an entire database between just two systems. If you need to transfer the database to more than one system, you should investigate clustering, replication, log-shipping or other features.

Although you can use Database Mirroring to copy a database from one system to another to provide security similar to a SQL Server Cluster, it doesn't require the same hardware on both systems. You just need to ensure that you have enough room to handle the database and log transfers for the source data.

Another difference between Clustering and Database Mirroring is that the latter technology doesn't alter the identity of either server. Both servers remain independent of each other. Depending on your needs, this is either a good thing or a bad thing. On the positive side, there are no tricky network or naming situations that you need to worry about. On the negative side, although the transfer of data is automatic, you need to either code your applications to detect the change or manually change the clients to point to the new data location when the primary system fails to the standby system.

A final comparison of Database Mirroring and Clustering is that you can mirror a single database — you don’t need to copy the entire Instance of SQL Server with all of its databases as in Clustering.

For Database Mirroring to work, the database you're mirroring must be in the FULL recovery model, where the system writes every change through the log. That makes sense, because the whole process is based around sending the log files from one system to another. What you need to be careful about are operations that avoid the log. Since BULK LOGGED records don't go through the log, they can't be transferred by the Database Mirroring Process.

The systems involved in the process need to trust each other. If you're on the same domain this usually isn't a problem, but if the systems aren't then you'll have to set up a security certificate between them. I'll show you how to do that in another tutorial.

Database Mirroring Terminology

There are a lot of terms in Database Mirroring, some of which are shared with other technologies, so the place to start is with the definitions of the key concepts. The first are the names of the servers involved in the process. The server that houses the database you want to mirror is called the Principal. The server that receives the data and acts as the backup is called the Mirror. You can involve a third server to watch the other two, which is called a Witness. If you use a Witness server, it can automatically set the Mirror to become the Principal for you. Otherwise, you'll have to manually do that. All of these servers are called Partners.

The second set of terms deals with the level of protection you're after, called the Safety Level. If you want the switchover from Principal to Mirror automatically, that's called High Availability. To do that the Principal needs to be able to talk to the Mirror constantly, and you'll need that third server (the Witness). If you are willing to cut over the systems manually, then you can use a mode called High Protection, where you don't have to include a Witness server. The last mode is High Performance, where you don't have to use a Witness. In this mode you have to manually fail over the system, and you aren't completely protected because some data might be lost in the interval of time between transmissions.

Example: Setting up Database Mirroring

The process for setting up Database Mirroring has three basic steps, although Books Online has about six to include the optional features. You can use a wizard to set up the mirroring, so I won't cover those screens in this tutorial. Instead I'll show you the command-line version of the same process.

Step One: Back Up and Restore the Mirrored Database

First, take a backup of the database you want to mirror on the server that you will designate as the Principal. Remember, the database must be in the FULL recovery model for the process to work. Transfer the backup file to the server you will designate as the Mirror, and restore it, but leave it in the WITH NO RECOVERY mode. That leaves the database open to more data from the log restore process. If you’re not familiar with restoring databases and the WITH NO RECOVERY mode, check the links at the end of this tutorial.

Set and Enable Endpoints on the Servers

Endpoints are a new feature starting in SQL Server 2005 that allow direct network port connections for processes and users. You'll need one endpoint on each of the servers involved, and then you'll need to turn the port monitoring on. For this tutorial, I'll just set up a Principal and a Mirror on two servers using the same port.

Here's an example that sets up an endpoint on a test server that uses TCP/IP port 5025:

CREATE ENDPOINT DBMirroringTest 
AS TCP (LISTENER_PORT = 5025)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = ENABLED);

This syntax creates and names an endpoint as a TCP listener on port 5025. It also specifies that this server will be a partner (not a witness), and that the communications between them will be encrypted.

I'm using a physically separate machine as the Mirror, so I can use the same port on that server. In fact, I can run the same statement again on that system to set up its endpoint:

CREATE ENDPOINT DBMirroringTest 
AS TCP (LISTENER_PORT = 5025)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = ENABLED);

If you want to try this out using two instances on the same server, change one of the port numbers above. Even though you are running multiple copies of the database server, the underlying Operating System is the same and that's where the port definitions in this case are.

Now that I have the ports, I have to tell them to listen on the network. I can do that with this command, run once on both Instances:

ALTER ENDPOINT DBMirroringTest STATE = STARTED; 

Define and Enable the Database for Mirroring

With the database backed up and restored and the endpoints created and listening, all I have left to do is enable a database for mirroring and in the process define the Principal and Mirror servers.

You'll need to obtain the fully-qualified domain name (FQDN) for the servers involved. My test servers are principalsvr.buckwoody.com and mirrorsvr.buckwoody.com. I used a couple of virtual servers for this test, so I made the names easy to follow.

In my case, I'm going to mirror a database called TestDB that is located on the principalsvr server. You can follow along on your servers, just make sure you watch the server and database names, and use the port numbers you set up earlier. I’ll start on the Principal server:

ALTER DATABASE TestDB
SET PARTNER = ’TCP://mirrorsvr.buckwoody.com:5025’;

Notice that the partner name (the Mirror) is prefaced with 'TCP://, and at the end I've got the port number I set up earlier.

Now I'll move over to the Mirror server, and you can do the same on yours. I repeat the same command, only I change the server name to point back to the Principal:

ALTER DATABASE TestDB
SET PARTNER = ’TCP://principalsvr.buckwoody.com:5025’;

I have one final command in this step. Earlier I spoke about the levels of safety. I have to select one of those levels to finalize the mirroring. There are two settings I can specify: FULL or OFF. With the FULL setting, I need either a Witness or at least a constantly connected network link, which maps to the High Availability or High Protection modes I mentioned. The OFF setting is for the lower level of protection, called High Performance. Since I've got a good network connection and no Witness, I'll stick with OFF. Keep in mind that this means I'll have to manually recover the system if I have a failure.

I'll switch back to the Principal server and run this code to set the safety level:

ALTER DATABASE TestDB SET SAFETY OFF;

That's all there is to it.

In future tutorials I'll explain how to manually recover the system when you're in this configuration. I'll also talk about other considerations in Database Mirroring. It’s important to note that basically what is happening is similar to log-shipping — in effect, the transactions from one system are constantly being “replayed” on the other system, which is NOT available for use. It’s being constantly restored, so the database isn’t open for users. There are ways around this (a little) which I’ll cover in another article.

InformIT Articles and Sample Chapters

To find out about how Database Mirroring compares with other High-Availability options in SQL Server, check out this section of Eric Brown's sample chapter, Enterprise Data Management in SQL Server 2005.

Books and eBooks

I cover more about High Availability strategies (including Database Mirroring) in my book, Administrator's Guide to SQL Server 2005.

Online Resources

For a more complicated treatment of this topic for using certificates and so on, check out this MSDN article.