Set Up Database Mirroring in SQL Server

I’ve been really excited by how enthusiastic my four year old son is to go fishing. There’s never a time when he isn’t in the mood to go catch some fish, although he still needs to be consistently catching fish or else he gets bored pretty quickly. Last Sunday we had talked about fishing on a small lake near our house, but as we were preparing to leave it started raining. It was a cool day and pretty windy, and I knew we’d get cold and wet fairly quickly so I asked him if he really wanted to go. Ever the fisherman, he assured me that he wanted to fish despite the poor weather. We ended up catching only a single fish, but it was his first crappie ever, so it was worth the trip.

I got a tip on another good panfish lake near our home, so we’ll be trying that one in the next couple of weeks. Hopefully the weather will cooperate a little better so we can have a more enjoyable trip. The key catching enough fish to keep small children interested is practice. Panfish are usually quite plentiful, so you need only to practice to learn where the fish like to congregate. Then you can keep your children happy for a couple hours, and make some great memories at the same time.

Practice is also important as a DBA, particularily when attempting a large project. As I have noted in a few of my recent posts, we are going to be doing a server migration at work in the next couple months. We have a brand new production server and need to move SQL Server, including SSIS and SSRS, over to the new server. Downtime isn’t a huge concern for my current employer based on our system configuration and requirements, but I’m still going to try to minimize it as practice for the future when it will be important. I’ve decided the method for moving the databases over to the new server will involve setting up database mirroring from the new server to the old. This will allow the servers to be pretty much in sync when it’s time to cut over. First I need some practice.

Today I’m going to demonstrate the steps I take to set up database mirroring for our Test environment. We aren’t going to be ready to cut over our Production environment for a few months, but I want to make sure I’ve got the process down before attempting it. The first step to set up database mirroring is to take a full backup and log backup of the database. I’m going to start with our largest database which has nine data files and is roughly 54 GB in size. Since this is our test environment I first need to change the database into Full Recovery mode.

USE MASTER;
ALTER DATABASE [App_Test] SET RECOVERY FULL;

Next I take a full backup and a transaction log backup of the database. This is occuring on the current Test environment SQL Server, which I’ll call the Principal for the remainder of this blog post.

BACKUP DATABASE [App_Test] TO  DISK = N'S:\SQLData\AppTestFull.bak' WITH NOFORMAT, NOINIT,  
NAME = N'App_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
GO

BACKUP LOG [App_Test] TO  DISK = N'S:\SQLData\AppTestLog.trn' WITH NOFORMAT, NOINIT,  
NAME = N'App_Test-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Now I’ll restore these backups on the new server, which I’ll call Mirror for the remainder of this blog post. I moved the backup files to a shared drive on a storage server so the Mirror server could access it. The one important option is to keep the database in NORECOVERY so that we can apply the additional transaction log backups and the database stays offline. A database that has been restored with NORECOVERY will show up in SSMS in the Restoring… status:

USE [master];
RESTORE DATABASE [App_Test] FROM  DISK = N'\\server\SQLBackup\AppTestFull.bak' WITH  FILE = 1,  
MOVE N'cwidb_Data' TO N'D:\DataFiles\App_Test.mdf',  
MOVE N'AppTest2' TO N'D:\DataFiles\AppTest2.ndf',  
MOVE N'AppTest3' TO N'D:\DataFiles\AppTest3.ndf',  
MOVE N'AppTest4' TO N'D:\DataFiles\AppTest4.ndf',  
MOVE N'AppTest5' TO N'D:\DataFiles\AppTest5.ndf',  
MOVE N'AppTest6' TO N'D:\DataFiles\AppTest6.ndf',  
MOVE N'AppTest7' TO N'D:\DataFiles\AppTest7.ndf',  
MOVE N'AppTest8' TO N'D:\DataFiles\App_Test8.ndf',  
MOVE N'AppTest9' TO N'D:\DataFiles\App_Test9.ndf',  
MOVE N'cwidb_Log' TO N'E:\LogFiles\App_Test_1.ldf',  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5;
GO

RESTORE LOG [App_Test] FROM  DISK = N'\\Server\SQLBackup\AppTestLog.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

Next go to the Mirroring tab of the database properties on the Principal server.  Click Configure Security…

A wizard will start to guide you through the setup. First it will ask if I want to include a Witness Server. This server watches the principal so you can automatically failover to the mirror if the principal becomes unavailable. Since I am only using mirroring to prep the Mirror server to be the Production server, I’m not going to set up a Witness.

The next screen allows us to set up some basic information about the Principal Server. I will leave the defaults for this.

The next screen is for setting up the Mirror server. Again, I will leave the defaults for this.

The next screen lets you specify accounts for the mirror process. Since the service accounts that run SQL on both these instances already have SQL logins with CONNECT permissions, I originally tried to leave these text boxes blank.  However, I got a connect error (at the end of the process) when I did this:

So I ended up filling in the boxes with the domain account running SQL:

Lastly I get a summary screen. Press Finish to run the Configuration.

After pressing Finish, it took a very short time for the configuration to complete:

Press Close and another message box will start up asking if I want to start mirroring. I do want to start mirroring, but first I need to change the Operating mode. It is currently set to synchronous, which is high safety without automatic failover. Since this is just to prep a server migration, I don’t want the mirror to be slowing down transactions on the principal server. I need to choose asynchronous, which means that transactions will occur on the principal server immediately, and then be transfered over to the mirror. I choose Do Not Start Mirroring:

Next I go back to the properties and change the operating mode to asynchronous:

At this point I chose to start mirroring:

That is how you set up mirroring.  Now when I am ready to migrate servers, I will have all the data already on the new server.