SQL Server 2005 Mirror Setup (A Quick Crib Sheet)

One of the most important features introduced by SQL2005 was the ability to mirror a database on a backup server. There are many articles written about this Mirror feature all covering it in great depth. However, what I did not find was a quick 1-2-3 on how to setup this useful feature. So, after much reading and analyzing, I came up with the following simple steps to setup a simple mirror.

Before getting into the detail, here are a few important items to note:

1. This article will only cover setting up a PRIMARY server and it's MIRROR. I am leaving out using a "witness" server (a third server) to keep these notes simple.
2. SQL Server will use the "service account" (the account the SQL Service is configured to run under) for authenticating between the servers. Because of that, the steps here will NOT work unless both servers are using the same domain account (if on a domain) or have the same username/password (if not on a domain) for the SQL service!
3. Make sure that your two servers have unrestricted connectivity to each other. If you are on the same network, this should not be a problem. However, if you are mirroring over the Internet, make sure that firewall rules are such that the servers can talk to each other and that the traffic between the servers on multiple ports will be allowed.
4. I am sure there is a way to do this "point-and-click". However, this procedure does it all with TSQL in SQL Studio.
   

So, with those details out of the way the steps to setup a mirror are:

On Both Servers

1. Create an endpoint for mirroring. This is the same command issued on "each" server.

Create Endpoint Mirroring_Endpoint
State= Started as TCP (Listener_Port=5022)
For Database_Mirroring (Role=Partner);

Notice above the port that will be used is 5022. If you need another port for each server to listen on, change this to suit your need.

On the PRIMARY Server

1. STOP any applications that are accessing your database. You need to make a clean backup and restore than on the mirror. If applications are putting through updates, your backup will be out of synch. Make a backup of the database AND the log.

Backup Database [YourDBName] to DISK='c:\sqldata2005\backup\YourDbName.bak' WITH FORMAT;
Backup Log [YourDBName] to DISK='c:\sqldata2005\backup\YourDbName_log.bak' WITH FORMAT;

On the MIRROR Server

1. COPY the backup files over to your MIRROR server however you can. For my example below, I copied the files into the SAME folder but on my OTHER server. Don't be confused by this... you MUST copy the files from server to server however you can!
2. Restore the backup with the following commands to keep it in "recovery mode".

Restore Database [YourDBName] from Disk='c:\sqldata2005\backup\YourDBName.bak' with NORECOVERY;
Restore Log [YourDBName] from Disk='c:\sqldata2005\backup\YourDBName_log.bak' with NORECOVERY;

3. You are now ready to "partner up" with the primary server (yes, you do it on the mirror server first.)

Alter Database [YourDBName]
Set Partner= 'TCP://YourPrimaryServer:5022';

Note if you changed the port above, you should change it here too. Also, "YourPrimaryServer" should be a fully qualified server name that resolves in your network. It could be the primary server IP or a full DNS name that resolves to the primary server IP. It's up to you! Remember, the MIRROR partners up with the PRIMARY!

Back on the PRIMARY

1. Finally, "partner up" on the primary server as well.

Alter Database [YourDBName]
Set Partner= 'TCP://YourMirrorServer:5022';

Note if you changed the port above, you should change it here too. Also, "YourMirrorServer" should be a fully qualified server name that resolves in your network. It could be the mirror server IP or a full DNS name that resolves to the mirror server IP. It's up to you! Remember, the PRIMARY partners up with the MIRROR!

All Done

That's it! If you didn't get any error messages when executing the above commands, you now have a PRIMARY server that MIRRORS over to a MIRROR server! Note, the MIRROR stays in recovery mode constantly.

Don't forget to restart your application so that it starts to again ready and update your PRIMARY.

Curious about how the mirror is progressing? Right-Click on the database you mirrored on the PRIMARY server and select TASKS, then LAUNCH DATABASE MIRROR MONITOR. Follow the instructions on screen to register your mirror database. Once registered, you can see the status of both PRIMARY and MIRROR databases.

Failover or Recovery?

If you need to failover to your mirror or recover from some failure of the primary, you'll need to google: "SQL 2005 Mirror Switching Roles" for plenty of information. An excellent place to start is Microsoft's own documentation on this topic available at http://msdn.microsoft.com/en-us/library/ms189850(SQL.90).aspx.