You are hereHow To: Setting Up Database Mirroring on SQL Server 2005

How To: Setting Up Database Mirroring on SQL Server 2005

By hagrin - Posted on 22 June 2010

After fixing a SQL Server naming issue, I was ready to continue on with my SQL Server 2005 database mirroring project for my client. The resources for setting up database mirroring are very light across the web so I decided I would help others out by writing out a "how to" guide. First, here are some assumptions that this guide uses which mimics the environment my client had for this project -

  • All of the servers involved are joined to the same domain.
  • You will be using a Principal server, a Mirror server and a Witness.
  • The Principal and the Mirror servers are the same version of SQL Server 2005 (in this case, all Standard Edition with SQL Server SP3).
  • The Witness server can be a version of SQL Express and does not need to be a full-blown version of SQL Server.
  • The database you want to mirror is in Full recovery mode.

Now, your server don't have to be on the same domain as you can use SQL Certificates; however, you'll find that database mirroring is much easier to setup if all the machines are on the same domain. The first step is to make sure that all of the SQL instances are running underneath the same domain account as opposed to the Local Account. The easiest way to accomplish this is to open up SQL Server Configuration Manager on each SQL instance, click on SQL Server 2005 Services, right click the SQL Server option, choose Properties and on the Log On tab choose "This Account" and enter in the credentials of the domain account you created to run your SQL services. When you are finished, click OK and your SQL services will restart.

Next, on your Principal server, you want to perform a Full database backup on the database you want to mirror. To do this, connect to the Principal server in SQL Server Management Studio, right click on the database, choose Tasks -> Backup, make sure the Backup type is set to "Full" and make sure you choose a Destination with a file extension of .bak. On the Options page, select the "Overwrite all existing backup sets" and then click OK to start the backup process. Once the backup is finished, you will want to connect to the Mirror server and restore the backup by right clicking "Databases", selecting "Restore Database", choosing "From device", clicking the browse button to the right, selecting your backup, checking the checkbox for your database and going back to the top and selecting the name of your database in the "To database" dropdown. Once that is completed, select the Options page, check the "Overwrite the existing database" option and then choose the Recovery state option that has "RESTORE WITH NORECOVERY" at the end and click OK to being the restoring process.

Once the full backup restoring process has been completed, you need to go back to your Principal server and perform a Transaction log backup. To complete this, you will use the same steps as the full backup except that for the "Backup type" you will select "Transaction Log" and then in the Destination box you will first remove the current destination and then Add a destination where you use a filename with a .trn file extension. Once the transaction log backup has been completed, connect to your Mirror server and restore the transaction log. To do this, you will right click on your restored database, choose Tasks -> Restore -> Transaction Log, choose "From file or tape", browse to your .trn backup, select the checkbox for your backup and then on the Options page choose the option with "RESTORE WITH NORECOVERY" at the end.

At this point you are now ready to configure your database mirroring. Connect to your Principal server, right click on the database you would like to mirror, select Tasks -> Mirror and select the "Configure Security" button. This will launch a wizard where you have to specify the names of your Principal, Mirror and Witness servers, connect to all of them and when you get to the "Service Accounts" page, simply click Finish and click Start Mirroring once the wizard has completed. At this point you should have successfully created a quorum and your database should be successfully mirrored and synchronized.

Hopefully, this guide will help you make your databases more redundant and help your organization attain a higher level of availability.