Recently I’ve been setting up mirroring of Microsoft SQL databases.  While the steps required are well documented on Technet (which is where I learned), they are spread over several pages.  There is no single list of steps so I decided to put one together documenting how I setup mirroring using SQL Server Management Studio.

What I’m not going to do is detail all the requirements or possible ways to set mirroring up.  For this information Technet is probably still your best resource.  So, the process below makes certain assumptions:

1. You’ve already installed and configured the same version of SQL on two servers.
2. These servers should have the drive setup so that the paths for database and logs files can match on both server. For example, if databases are stored at D:\SQLData on the primary server, this path needs to be available on the secondary server.
3. These servers both members of the same domain.  This is not a hard requirement of mirroring, but it does make the process easier.  If your servers are in different domains see TechNet for the process of setting up security.
4. The SQL server service is running as the same domain user on both servers. Again, not a hard requiremnet but it’s how I’ve done it.
5. I am not including setting up a witness server or automatic failover.  For a configuration with these options, consult technet.

The basic process involves 3 steps, backing up from the primary, restoring to the secondary and configuring the mirroring.

Backup the database on the Primary server

1. Open SQL Server Management Studio and connect as a user with appropriate permissions (probably sysadmin).
2. Right-click on the database you wish to mirror and select Tasks->Back Up…
3. Add a backup destination file with a name like database.bak.
4. Leave the default options selected.
   1. Backup type: full
   2. Overwrite media:
   3. Backup to the existing media set
   4. Append to the existing backup set
5. Click OK and let the backup run.
6. Right-click on the database and again choose Tasks->Back Up…
7. Change the Backup type: to Transaction log.
8. Check that the destination file is the same.
9. Click OK and let it backup again.

Restore the database on the Mirror (destination) server with “No Recovery”

1. Copy the backup file “database.bak” to the Mirror server, preferably in the default SQL backup directory.
2. Open SQL Server Management Studio and connect to the Mirror server as a user with appropriate permissions (probably sysadmin).
3. Right-click the “Databases” object and select “Restore Databases.”
4. Under “Source to restore” click the “From device” radio button, then click the browse button to the right.
5. Click Add and select the backup file you copied from the Primary server. Click OK.
6. Select both the full and transaction log backups you created earlier.
7. Under the Destination for restore, use the drop down box to select the database name. This should match the database name on the primary server.
8. Change to the Options page.
9. Change the recovery state to “RESTORE WITH NORECOVERY” as shown below.
   
10. Click OK to restore the database.

Configure Mirroring

1. Return the SQL Management studio connected to the Primary server.
2. Right-click on the database and choose Tasks->Mirror…
3. Click the Configure Security button.
4. Click next on the starting page.
5. Choose whether or not to include a witness server instance. For this example we will choose no. Click next.
6. Click Next on the Principal Server Instance page.
7. Select or browse so the “Mirror server instance”  shows the name of the desired mirror server.
8. Click the Connect button to the right. Enter credentials (if necessary) to connect again with sysadmin privileges.  Click connect.
9. Enter the correct service accounts.
   1. I’ve always configured mirroring between SQL servers in the same domain, with SQL services running as the same user.  So I always enter something like the below.
      
10. Click Next then Finish.
11. You should then get a Success box. Click close.
12. You will then be prompted with the below message. If you want mirror using the default Operating mode (High Safety without automatic failover) then click Start Mirror. For this example I will choose Do Not Start Mirroring.
    

13. Change to the desired Operating Mode. (Below I’ve changed to High Performance which I believe requires SQL Enterprise)

14. Click the “Start Mirroring” button.

15. The Status should change to the below:

Congratulations, your database is now mirrored.

Notes:

• With some backup software it may be necessary to recreate you backup jobs after configuring mirroring.