Click here to Skip to main content
14,365,336 members

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

Rate this:
4.60 (17 votes)
Please Sign up or sign in to vote.
4.60 (17 votes)
13 Sep 2010CPOL
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.

Image 1

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.

    Image 2

    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.

    Image 3

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

    Image 4

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

    Image 5

    Image 6

  6. Now choose Principal SQL Server Instance:

    Image 7

  7. Now choose Mirror SQL Server Instance:

    Image 8

  8. Choose a Witness Instance:

    Image 9

  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.

    Image 10

  10. Completing the Wizard:

    Image 11

    Image 12

  11. Start the mirroring:

    Image 13

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

Image 14

The Principal SQL Server

Image 15

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.


Image 16 Image 17 Image 18 Image 19 Image 20 Image 21 Image 22 Image 23

License

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

Share

About the Author

Raymund Macaalay
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 11:42
memberRockie(aka Collapse Troll)26-Jan-16 11:42 
QuestionCan you please give the clear information on how to give FQDN for SQl Server Instance Pin
ayubkhancse29-Dec-14 22:07
memberayubkhancse29-Dec-14 22:07 
AnswerRe: Can you please give the clear information on how to give FQDN for SQl Server Instance Pin
Raymund Macaalay4-Jan-15 8:51
memberRaymund Macaalay4-Jan-15 8:51 
GeneralMy vote of 4 Pin
AEMLoviji6-Jan-13 20:27
memberAEMLoviji6-Jan-13 20:27 
GeneralMy vote of 4 Pin
AEMLoviji6-Jan-13 20:26
memberAEMLoviji6-Jan-13 20: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.

Technical Blog
Posted 13 Sep 2010

Tagged as

Stats

123.4K views
37 bookmarked