Database Mirroring in Microsoft SQL Server 2005
Published: 28 Aug 2007
This article provides a brief overview of database mirroring in SQL Server 2005.
by Amit Bansal
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 29555/ 77


Fast failover with minimal data loss has traditionally involved higher hardware cost and greater software complexity. Database mirroring, however, can fail over quickly with no loss of committed data, does not require proprietary hardware, and is easy to set up and manage.

Poor man's clustering solution, cost effective failover solution, whatever you call it, Database Mirroring is one of the most talked about features in SQL Server 2005.

In DB Mirroring, a SQL Server 2005 instance continuously ships its transaction log records to a copy of the database on another SQL Server instance. So you have the principal server and the mirror server. However, the names are relative since the roles can be switched over in case of a failover. These are considered as partners. We may also have a third server called the witness server allowing for automatic failover. With the presence of the witness server, a quorum is formed. If the principal server goes down, the mirror server can take the role of the principal in a few seconds after getting a confirmation from the witness server. Not to mention, in the absence of a witness server the failover has to be performed manually either through SQL Server Management Studio or T-Sql.


There are some prerequisites and key points to understand:

1. The principal database must be in full recovery model.

2. The mirror database has to be created from a full backup of the principal server and should be restored in "Restore with Norecovery" model. It is followed by a restore of transaction log backup of the principal database so that the log sequence numbers of the mirror and the principal database are in synch with each other.

3. The mirror database must have the same name as the principal database.

4. The mirror database cannot be accessed directly; however snapshots of the mirror database can be taken for read only purposes.

5. The mirroring partnership can run in synchronous (high availability mode) or asynchronous (high performance mode).

6. As mentioned earlier, failover can be automatic in presence of a witness server or else manual.

7. In case of a failover (manual, automatic or forced), client applications will be automatically redirected to the mirror server provided you use AD.NET or SNAC (SQL Native Client). Look at an example connection string:

Listing 1

"Data Source=PrincipalServerName;Failover Partner= MirrorServerName; 
Initial Catalog=AdventureWorks; Integrated Security=True;"

8. DB Mirroring is available in Enterprise, Developer and Standard Editions, however, please refer to Microsoft website for a comparison chart as some features are not available in the Standard Edition. SQL Server Workgroup and Express Editions can only be used as witness servers.

Steps and Setup

1. Identify your principal and mirror server. Optionally, you may want a witness server.

2. For practice purposes, the above 3 instances can be on the same server.

3. Take a full backup of the database on the principal server.

4. Restore the backup on the mirror server with "Restore with NoRecovery" option. Please remember the database name has to be the same.

5. The servers or instances involved in DB mirroring must trust each other. Each instance login must have rights to connect to the other mirroring server and to its end points.

6. The next step is to create database mirroring endpoints. You need admin rights for this. Endpoints need to be created on partner servers as well as the witness server, if any. You can do this using the Configure Database Mirroring Security Wizard, which you can invoke by clicking the Configure Security button on the Mirroring page of the Database Properties dialog. Or execute the CREATE ENDPOINT command using T-SQL. If you are setting up database mirroring on a domain, and all SQL Server instances use the same service login and password, you need not create logins on each server. This applies to workgroup model as well. If all SQL Server instances use the same service login and password, you do not need to create logins on the servers. Just leave the logins blank on the Configure Database Mirroring Security Wizard. Each database endpoint must specify a unique port on the server. When SQL Server instances are on separate machines, these port numbers can be the same. The Configure Database Mirroring Security Wizard will automatically suggest port 5022. If any of the SQL Server instances are on the same machine, each instance must have a distinct port and the port numbers must be unique.

7. Once you have set up the servers and the endpoints, you can start database mirroring. You again need admin rights. This can be done from Mirroring page of the Database Properties dialog or T-SQL.

Happy Mirroring


PeoplewareIndia UserGroup

The author of the article is Amit Bansal, Trainer Consultant & Technology Evangelist at PeoplewareIndia, a unit of eDominer Systems P Ltd. He blogs at and maintains a website.


In this article you have learned some of the aspects involved with database mirroring in SQL Server 2005.

User Comments

Title: good introduction   
Name: karan
Date: 2009-02-20 2:50:00 AM
It is very nice to know the basic of Mirroring Database

give more issues while setup
Title: Database Mirroring in Microsoft SQL Server 2005   
Name: Parveen Siwach
Date: 2009-01-19 8:42:13 AM
Looking forward to more articles on Database Mirroring Failover by you. Good Effort. All the Best!!!!

Thanks Amit
Title: Good Intro   
Name: Shashi Kumar Singh
Date: 2008-05-29 8:17:48 AM
It is very nice to know the basic of Mirroring Database.

Title: Mr   
Name: Balaji
Date: 2008-04-30 7:03:31 PM
Thanks for the knowledge transfer
Title: THANKS A LOT.....   
Name: sukhmani
Date: 2008-04-15 3:43:34 AM
hi amit,
very good article...
thanks a lot...
all d best...
Title: Mr.   
Name: Sen
Date: 2008-01-27 4:28:50 AM
Very Interesting. Looking forward to more articles by you. Good Effort. All the Best!
Title: Mr.   
Name: Ambalavanan Chithambaram
Date: 2007-12-21 1:54:52 AM
My special thanks to you for such a apt presentation of mirroring concepts.Please do keep writing so we people will keep on reading and utilising it.
All the best for your noble efforts. Please do keep continuing...
Title: Mr.   
Name: Jeet
Date: 2007-11-28 5:53:06 AM
Indeed superb, I was lucky to be part of its demonstration too by Amit.....kudos to Amit!
Title: Mr   
Name: Visagan
Date: 2007-09-20 3:03:13 AM
Hi Amit,
Hi Amit,
iam one of the guy who attended the Training in Bangalore,
It was a good training and it helped me to learn SQL Server better than beffore.
Title: Mr   
Name: Subhrendu Guha Neogi
Date: 2007-09-14 9:47:41 AM
Hi Amit,
Its too good and I hope you will write such again and again.
Title: Mr   
Name: Manjunath Sinnad
Date: 2007-09-12 6:51:33 AM
Hello Amit,

This is wonderful document.Please send link to doc on performance tuning.
Title: Hello Amit   
Name: Raj Mohapatra
Date: 2007-09-07 5:03:33 AM
Hi Amit,

Good Article!!!
On my Rank 10/10.
Keep it up.

Title: Mr.   
Name: pradeep phuloria
Date: 2007-09-02 1:26:11 PM
Hi, amit

good article. Keep writing.

Date: 2007-08-30 4:30:22 AM
Title: Mr.   
Name: A. Jhunjunwala
Date: 2007-08-30 1:56:43 AM
It was nice.
Title: Ms.   
Name: Mandira
Date: 2007-08-30 1:54:24 AM
I work as SQl DBA, so the article was pretty useful or me.
Title: Mr.   
Name: Soumitra
Date: 2007-08-30 1:52:55 AM
The article was lengthy but I liked it very much.
Title: Mr.   
Name: Arnab
Date: 2007-08-30 1:50:54 AM
Interesting and informative.
Title: Ms   
Name: Suks
Date: 2007-08-30 1:44:07 AM
Very detailed article. Gives a good insight into the SQL DB Mirroring. Good piece of work!
Title: Mr   
Name: Gaurav Bhattacharya
Date: 2007-08-30 1:43:42 AM
Very informative
Title: Good Article on Data Mirroring   
Name: Jayant Jindal
Date: 2007-08-30 12:04:04 AM
Very Good Article to get an insight for Data Mirroring in SQL Server
Title: Mr.   
Name: Rahuk
Date: 2007-08-28 12:56:02 PM
neat and clean

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2018  |  Page Processed at 2018-12-14 2:45:56 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search