Tag Archives: Database-Mirroring

Database Replication in MS SQL Server

18 Aug

Introduction:

For high availability of data in your SQL server, you might require a replica of your database server. To server this purpose you can use MS SQL Server’s mirroring functionality. By using the mirroring feature we can create a replica of our database being synchronized real-time.  Your same data will be saved at two different servers. In MS SQL Server Mirroring there are three different modules i.e.

  • Principal Server (Primary)
  • Mirroring Server(Secondary Server)
  • Witness Server

Practically all three servers are on different machines.  Principal is the server whose database replica will be being synchronized at the Mirroring Server. Witness server performs the failover duties, incase principal server goes down Witness Server will perform automatic failover to make mirroring as principal server. Supporting automatic failover is the only role of the witness.

Mirroring Architecture

Mirroring Architecture

Now how to install database mirroring in MS SQL Server?

Pre-Requisites:

  1. MS SQL Server Enterprise/Standard/Workgroup Edition installed on Principal and Mirroring Server while on Witness Sever we can have any SQL Server Edition installed.
  2. Principal, Mirroring and Witness Server must on different machines.
  3. On each server database service should be running with same windows/active directory account and password. This is very important please be careful about this point otherwise you will get errors later.
  4. Run All SQL Servers Service with Same Account

    Run All SQL Servers Service with Same Account

    SQL Server Service Logon

    SQL Server Service Logon

    Re-Start Services

    Re-Start Services

  5. Primary Database (Principal) is in Full Recovery model.
 Full Recovery Database Model

Full Recovery Database Model

Installation Step-By-Step:

Take the back up of desired database (which needs to replicated/mirrored) on the Principal SQL Server.

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

RESTORE WITH NORECOVERY Option

Restore DB at Mirroring Server

Restore DB at Mirroring Server

After you click “OK” button, you will see it’s in a Restoring mode 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.

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

On the Include Witness Server screen, select “Yes” and click “Next” Button.

Now choose Principal SQL Server Instance:

Now choose Mirror SQL Server Instance:

Choose a Witness Instance:

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.

Completing the Wizard:

Start the mirroring:

Note: In case of any error check if all the SQL server services are running with same account.

Credits: I have learnt Mirroring from Code Project, I have also taken some images from that article. So I am full credit to , the Arthur of Article at Code Project.