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.

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 http://ABwrites.blogspot.com and maintains a website.

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