I plan on writing a series of posts on database mirroring, this is the first. Database Mirroring is a new feature of SQL Server 2005 and I have been configuring and testing it lately so I thought I would write a short post to document what I have learned.

This post will walk through configuring mirroring, I will be using my laptop and a couple of SQL 2008 instances for this example, so this is nothing more than a demo, but the process should be the same for two separate SQL Server instances on two separate servers.

The key to configuring database mirroring as with most things is in the preparation.

Pre – Preparation Steps

If you are following along and you have a database you want to mirror, then you don’t need this step, I have included this section for completeness, I need a database to mirror. On my primary server I run the following script safe in the knowledge  my  model database is in Full recovery mode.

/*CREATE A DATABASE TO TEST MIRRORING on Primary Instance */

USE [master]
GO

CREATE DATABASE [Mirror_Test] 

OK so that’s the pre-preparation steps complete. I have a database setup in full recovery mode that I want to mirror.

Preparation Steps

In order to prepare for mirroring at a minimum you need a full backup of the principal database and a transaction log backup of the principal database, both of these files need to be restored creating the mirrored database on the secondary instance.

So we create a full backup, followed by a transaction log backup on the on the primary, soon to be principal database:

/*Take a full backup of Primary database */

BACKUP DATABASE [Mirror_Test] 
TO  DISK = N'C:\Backup\Mirror_Test.BAK' 
WITH NOFORMAT, NOINIT,  NAME = N'Mirror_Test-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

We then need to take a transaction log backup of the principal database

/*Take a T/LOG backup of Mirror_DB*/

BACKUP LOG [Mirror_Test] 
TO  DISK = N'C:\Backup\Mirror_Test.TRN' 
WITH NOFORMAT, NOINIT,  NAME = N'Mirror_Test-Transaction Log  Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

These files need to be copied to the secondary/other server to restore and create the mirrored database. My other instance is on the same physical server as the instance housing the principal database. So i don’t need to copy any files. Assuming you have the full backup and transaction log file available, I then restore the FULL Backup file creating a database with same name, I need  to specify the WITH MOVE statement as my databases are on the same physical server and hence the secondary mirrored database files need to be in a different location, so I include that in the restore statement:

RESTORE DATABASE [Mirror_Test] 
FROM  DISK = N'C:\Backup\Mirror_Test.BAK' 
WITH  FILE = 1, 
 MOVE N'Mirror_Test' TO N'C:\DATA\SQLMIRROR\Mirror_Test.mdf',  
 MOVE N'Mirror_Test_log' TO N'C:\DATA\SQLMIRROR\Mirror_Test_1.LDF',  
 NORECOVERY,  NOUNLOAD,  STATS = 10
GO

My next step is to restore the transaction log backup:

/*Restore the log backup taken, again leaving the DB in a recovering state*/

RESTORE LOG [Mirror_Test] 
FROM  DISK = N'C:\Backup\Mirror_Test.TRN'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

As you can  hopefully see from the scripts, I left the mirrored database in  standby mode by specifying NORECOVERY in my restore script. This is needed to allow me to setup mirroring. After the restore we have a copy of both databases on both instances with the secondary, mirrored database is a recovering state, we can now configure mirroring.

Configure Mirroring

On the instance hosting the primary database right click on the database select <Tasks><Mirror>

This takes us to the Database Properties dialog box and the <Mirroring> tab.

Click on the <configure security> button

This takes us to the <Configure database mirroring security wizard>

click <Next>

If you want to use a witness server, this is where you specify which instance will be the witness, I am not using a witness for this demo, but if I were to use Mirroring as a high availability option for my database in order to use automatic failover you need to have a witness server.

Click <Next>

On the next screen you specify the principal server. I’m taking the default values here.

Once your done here, click <Next>

One the Mirror Server instance screen, the first thing you need to do is connect to the mirror instance. You quote from the wizard:

“You must connect to this server instance using a login or account with the necessary permissions to save the security configuration before continuing this wizard.”

Ensure you have the correct instance in the <Mirror Instance Server box> and click <Connect> connect to the instance with necessary credentials. Again I have taken the default values here for port number and endpoint name. Because both instances are on the same physical server in my case I have to specify different port numbers. If they were on different physical server you could use the same port numbers.

On the next screen you specify   the service accounts for the principal and mirror instances. Click <Next>

Click <Finish> on the <Complete Wizard> screen.

The database mirroring endpoints then get created

When that is done click <Close>

You will then be prompted to <Start Mirroring> or <Do Not Start Mirror>. If you want to start mirroring select it.

Notes

It’s probably a good idea not to interfere too much with the business that mirroring configuration be run out side of core hours because the overhead of configuring mirroring can have an adverse affect on database performance.

When implementing this on my laptop with two SQL 2008 instances I had no end of fun with my Firewall and it blocking the ports, a small config change to the firewall soon sorted that out though.