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

This entry was posted in SQL Server 2005. Bookmark the permalink.

4 Responses to SQL Server 2005 Database Mirroring

  1. Unknown says:

    http://www.batteryfast.com/asus/f3.htm Asus f3 Battery http://www.batteryfast.com/toshiba/pa3594u-1brs.htm Toshiba pa3594u-1brs Battery http://www.batteryfast.com/toshiba/pa3593u-1bas.htm Toshiba pa3593u-1bas Battery http://www.batteryfast.com/toshiba/pabas111.htm Toshiba pabas111 Battery http://www.batteryfast.com/dell/xps-m1530.htm Dell xps m1530 Battery http://www.batteryfast.com/hp/tx2000.htm Hp tx2000 Battery http://www.batteryfast.com/hp/pavilion-tx1000.htm Hp pavilion tx1000 Battery http://www.batteryfast.com/dell/xps-m1730.htm Dell xps m1730 Battery http://www.batteryfast.com/hp/pavilion-dv6.htm Hp pavilion dv6 Battery http://www.batteryfast.com/toshiba/pa3395u-1brs.htm Toshiba pa3395u-1brs Battery http://www.batteryfast.com/toshiba/pa3421u-1brs.htm Toshiba pa3421u-1brs Battery http://www.batteryfast.com/toshiba/pa3451u-1brs.htm Toshiba pa3451u-1brs Battery http://www.batteryfast.com/toshiba/pa3399u-1brs.htm Toshiba pa3399u-1brs Battery http://www.batteryfast.com/toshiba/pa3399u-2bas.htm Toshiba pa3399u-2bas Battery http://www.batteryfast.com/toshiba/satellite-a100.htm Toshiba satellite a100 Battery http://www.batteryfast.com/toshiba/satellite-m40.htm Toshiba satellite m40 Battery http://www.batteryfast.com/toshiba/satellite-m45.htm Toshiba satellite m45 Battery http://www.batteryfast.com/toshiba/satellite-m55.htm Toshiba satellite m55 Battery http://www.batteryfast.com/toshiba/pa3356u.htm Toshiba pa3356u Battery http://www.batteryfast.com/toshiba/pa3356u-1bas.htm Toshiba pa3356u-1bas Battery http://www.batteryfast.com/toshiba/pa3356u-1brs.htm Toshiba pa3356u-1brs Battery http://www.batteryfast.com/toshiba/pa3356u-2brs.htm Toshiba pa3356u-2brs Battery http://www.batteryfast.com/toshiba/pa3456u-1brs.htm Toshiba pa3456u-1brs Battery http://www.batteryfast.com/toshiba/portege-m500.htm Toshiba portege m500 Battery http://www.batteryfast.com/toshiba/pa3451u.htm Toshiba pa3451u Battery http://www.batteryfast.com/toshiba/pabas067.htm Toshiba pabas067 Battery http://www.batteryfast.com/toshiba/pa3465u-1brs.htm Toshiba pa3465u-1brs Battery http://www.batteryfast.com/toshiba/pa3534u-1brs.htm Toshiba pa3534u-1brs Battery http://www.batteryfast.com/toshiba/pa3533u-1bas.htm Toshiba pa3533u-1bas Battery http://www.batteryfast.com/toshiba/satellite-a205.htm Toshiba satellite a205 Battery http://www.batteryfast.com/toshiba/pa3534u-1bas.htm Toshiba pa3534u-1bas Battery http://www.batteryfast.com/uniwill/255-3s4400-g1l1.htm Uniwill 255-3s4400-g1l1 Battery http://www.batteryfast.com/uniwill/un255.htm Uniwill un255 Battery

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s