Mirroring a SQL Server Database is not as hard as you think

Mirroring a database is not really a hard task to do. You just need these easy and simple 11 steps to start mirrored SQL Server. Doing it will not just improve your disaster recovery capabilities on your application, but also you are leveraging the high availability database mirroring feature you find in SQL Server 2005 and above, which means it will allow failover of database in the event you lose your main SQL Server.

Before starting this, be sure that you have 3 SQL Servers in different locations for best results. 2 Servers need to have the identical SQL Server instance which means the same version (either Standard or Enterprise) also it's highly recommended that also the service pack and if any cumulative updates are the same on both servers. But for the third server, it can be SQL Server Standard, Enterprise, Workgroup, or Express. Witness Server will be the one pinging the other 2 servers if there's something wrong. This is the server that has the ability to recognize whether to initiate an automatic failover. This will not contain any database, that's why it's nonsense to use a SQL Server other than Express edition.

1. Verify the following:
   1. You have 3 SQL Servers for Principal, Mirror and Witness
   2. SQL Server is using an Active Directory account. Ideally, use the same account for all SQL Servers.
   3. Primary Database is in Full Recovery model.
2. Back up the database on the Principal SQL Server.
3. Create a database with the same name from the Principal SQL Server on the Mirroring SQL Server, then restore the backup on the Mirroring SQL Server with the option to Overwrite the existing database checked and RESTORE WITH NORECOVERY option.

   

   You will notice it's in a Restoring mode. Don’t panic, this is normal as you have chosen the NORECOVERY option and it will be in a permanent Restoring state to prevent users from accessing the database. It will be only user accessible if the database fails over to the Mirror and now the old Principal will go to the recovering state.

   

4. Start the mirroring configuration process on the Principal SQL Server. Right-click the Database –> Properties –> Mirroring and click Configure Security.

   

5. On the Include Witness Server screen, select Yes and click next.

   

   

6. Now choose Principal SQL Server Instance:

   

7. Now choose Mirror SQL Server Instance:

   

8. Choose a Witness Instance:

   

9. Now enter the SQL Server Service Accounts for each SQL Server Instance, but if all of your SQL instances are using the same account, then just leave it blank.

   

10. Completing the Wizard:

    

    

11. Start the mirroring:

    

Hooray! you have mirrored your SQL! Go to both servers and it should look like this now.

The Principal SQL Server

The Mirror SQL Server

Note: You might find an issue when you start mirroring and encounter this error
The mirror database, “YourDatabaseName”, has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

As the error suggests, you need to backup the Principal SQL Server Transaction Logs and Restore it to the Mirroring SQL Server using the same restore options when you restored the database. If this happens, you can cancel the wizard and start configuring again after this step from step 4.