Mirroring Database In SQL Server

Pankaj Sapkal
Jul 12, 2017
5.3k
0
2
Article

The steps and importance of Mirroring is given below step by step,

To get high availability to our server after clustering we do Mirroring.
It’s a very effective failure option at database level without using or creating any Log shipping or any use of Sql Agent.
It’s again a real time log shipping solution.
Whatever modifications are done on Principal server or database the effect is copied to mirror server or database, but it doesn't show any acknowledgment that copy is done to Mirror server.
Whenever user makes any changes to database (db) it's immediately not written on database, it's written in db_log buffer file which is stored in special location in memory or disk.

The changes reflected - From log buffer file to Transaction log file which is called as Hardening.

Usually we have to database server for mirroring

But when any automatic failure is there then we need another server who will monitor the principal server and give the notification to Mirror server about principal server failure or the server goes down.

Navigation to open Sql Server Management Studio,

Windows Start > All programs > Microsoft Sql Server 2008 > Sql Server management studio
Connect with three servers
Principal, Mirroring,and Witness

First we will go on the property of Principal Server Database (we will see her the 3 options which will describe how we can increase the performance)

First step right click on database > Property
Here you will find the window were the options are mentioned in Operating Mode.

Options are mentioned below,

High Performance Asynchronous
whatever changes are made on Principal Db Server it gets saved in Log buffer of Principal Db and the same copy goes to Mirror db for the changes but the principal server doesn’t wait for the acknowledgment about the changes so, no Synchronous in data. It is risky to maintain data.
High safety without automatic failover (Synchronous)
Both partners (Principal and Mirror Db) wait for Synchronous because Principal server wait for the acknowledgment about the changes made into Mirror Server db through the log Buffer.
High Safety with automatic failover
Require a witness server instance who will keep monitoring/checking for principal server failure or goes down and giving the notification to Mirror database.

To start with Mirroring first we have to take a full backup for Principal Server Database.

Right click on Principal server db > Tasks> Backup

We will take a full backup and give the destination path

To verify the backup is finished go on the option on same page and click Verify backup when finished Check box under Reliability,

After completion of Backup a window will pop up with a message “The backup of database ‘test’ completed successfully”

Know the next to step is to restore the database into Mirror Server.

SQL Server

Then take the backup from the specified location from the device and give the name of the database and click the Restore check box.

Click on the option on the same window

IMP NOTE

While restoring the database it's important that we should recover it in NO RECOVERY MODE .

After completion of Restoration a window will pop up with a message “The Restore of database ‘Mirror test’ completed successfully”

Know the restoration is completed lets start with Mirroring

Right Click on Principal Server Database > Properties > Mirroring > Configure Security

A wizard window will open click next it will

“Do you want to configure security to include a witness server instance” Click Yes.

Click Next

Click Next (Principal Server Instance)

Click Next(Mirror Server Instance) Click connect and configure it

Click next(Witness server instance) Click Connect and configure it

Click Next - They will ask about the service Account for Principal, Mirror, and Witness but we will not give any name because we are using default instance name.