Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

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

Rate me:
Please Sign up or sign in to vote.
4.60/5 (17 votes)
13 Sep 2010CPOL3 min read 135K   37   5
Easy and simple 11 steps to start mirrored SQL Server

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.


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/

Comments and Discussions

 
QuestionThank You very much! Pin
Rockie(aka Collapse Troll)26-Jan-16 10:42
Rockie(aka Collapse Troll)26-Jan-16 10:42 
QuestionCan you please give the clear information on how to give FQDN for SQl Server Instance Pin
ayubkhancse29-Dec-14 21:07
ayubkhancse29-Dec-14 21:07 
AnswerRe: Can you please give the clear information on how to give FQDN for SQl Server Instance Pin
Raymund Macaalay4-Jan-15 7:51
Raymund Macaalay4-Jan-15 7:51 
It is the Fully Qualified Domain Name, the complete address ie. yourserver.yourcompany.com
GeneralMy vote of 4 Pin
AEMLoviji6-Jan-13 19:27
AEMLoviji6-Jan-13 19:27 
GeneralMy vote of 4 Pin
AEMLoviji6-Jan-13 19:26
AEMLoviji6-Jan-13 19:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.