SQL Server 2005 Database Mirroring

Database mirroring is a new high availability feature that was added to SQL Server 2005 (and enabled by default in SP1). It works at the database level rather than the instance level (like fail-over clustering). It requires SQL Server 2005 Standard Edition or Enterprise Edition (except for the Witness server, which can be Express Edition).

It is an attractive alternative to fail-over clustering for many scenarios, since it does not require Windows Clustering or cluster-certified hardware. It also eliminates the single point of failure of the shared storage and it allows much faster fail-overs (in the 5-10 second range) than fail-over clustering. It does require twice the storage space, since their are two copies of the database, and it requires SQL Server licenses for both the Principal and the Mirror. With fail-over clustering, you do not have to pay for the SQL Server license(s) for the passive node.

In order to set up database mirroring, the database that will be your Principal database must be running in the Full recovery model.  You then take a full backup of the database, copy the backup file to the server where the mirror will be running. Next, you restore the database to the desired location on the server (which should use the same drive letters and paths as the principal), with NO RECOVERY (so the database is still in a recovering state).

Depending on how long the backup/copy/restore sequence took (and how busy your database is) you may want or need to take one or more transaction log backups, and then copy/restore them in order, making sure you restore each one with NO RECOVERY.

Then, you need to configure the endpoints and security between the Mirror, Principal and Witness. Finally, you enable the mirror and wait for the Principal and Mirror to get synchronized. You may think you are done, but you have a few more steps to complete.

First, you need to deal with the "Orphaned Users" problem, where the SID of the Server login does not match between the Principal and the Mirror. What you need to do is use CREATE LOGIN to add a new server login on the server where the Mirror database lives that has a matching loginname, password and SID as the login on the server where the Principal database lives.

Run this to get the SID:

SELECT * FROM sys.server_principals

Then use this to create the login:

CREATE LOGIN <loginname> with password = <password>, sid = <sid for same login on principal server>,…

Finally, you need to modify your connection string to something similiar to what you see below:

— Sample connection string
"Server=Partner_A; Failover Partner=Partner_B; Database=AdventureWorks; Network=dbmssocn"

Technorati Tag: SQL Server