Database Mirroring

Introduction

This blog covers the High Availability Solutions provided by SQL Server i.e. Database Mirroring.

Database mirroring is a solution in SQL Server by which we can have the redundant copy of the database (Principal server) available on another server (Mirror Server) & incase of any disaster we can can bring the database online from another server

Let’s Start with Database Mirroring now:-

Part1

Mirroring Overview

Database Mirroring is one of the best options provided by SQL Server. It is implemented on per database wise. Database only configured with Full Recovery models can only be used in mirroring.

Hence Simple & Full Logged recovery models are not supported in database mirroring...

Also, database mirroring cannot be implemented on system databases.

Database mirroring contains the two copy of the database that generally resides on two different instances of sql server. All the client requests are handled by one instance, which is called Principal server. The other instance acts as standby server. When the mirroring session is fully 'SYNCHRNISED' standby server acts as HOT standby server with no loss of data & when session is not SYNCHRONISED the stand by server acts as warm server with possible loss of data.

 Benefits

Data Protection: - Database mirroring provides complete protection to the data. Complete redundancy of data is maintained either the operating mode is High Safety or High performance.

Database Availability: - In the event of any disaster, Database will be available as soon as possible. When the database is running in High safety mode with Automatic Failover, automatic failover will occur & Standby server will acts as Principal server without loss of data & start serving the requests to clients. In other mode we need to force the service to the standby server to make it principal server with possible loss of data.

Productions Database availability during Upgrades: To reduce the downtime of mirrored database, we can upgrade the instance of sql server which is participating in database mirroring, sequentialy.

Database Mirroring, behind the scenes

 The Principal and Mirror servers act as partners in database mirroring session. There are two roles i.e. Principal role & Mirror role. The partner who owns the Principal role is known as Principal server & its copy of the database is known as Principal database. All the client requests will be full filled by Principal databases. The partner who owns the mirror role will act as Mirror server & its copy of database is known as mirror database.

Database mirroring involved redoing of every insertion, updation & deletion occurred at Principal database onto the mirror database ASAP. This is done by sending the Active transaction log to the mirror server & it applies the changes in the mirror database in sequence as soon as possible.

Its works in two operations:

1) Synchronous: - Database mirroring acts in this mode send the active transaction log to the mirror database & waits for its confirmation. This guarantees that both the copies of database are synch with each other at any given point of time. But we need to compromise with the performance in this mode.

 2) Asynchronous: - Database mirroring acts in this mode doesn’t wait for the confirmation from the mirror server. This maximizes the performance of the database.

There are two modes in mirroring. One is High Safety mode which support Synchronous mode. Once mirroring starts with this mode, mirror server tries to synch the mirror database with the Principal database as soon as possible. Once both the databases are synched transactions are committed on the both the server at same time. If any request comes to principal server it just send the transaction to the mirror server & waits till it gets the confirmation from the mirror server. Once it receives the confirmation its sends the confirmation to the client about the change.

The second operating mode is High performance which supports Asynchronous mode. The mirror server try to write the logs send by the Principal server as soon as possible. Generally the gap between the principal & mirror server is small but yes it can be large if the principal server is under too much work pressure.

In this mode Principal server does not wait for the acknowledgement main from the mirror server. As soon as principal send the log information to mirror server, it send the confirmation to the client at the same time. As Principal server doesn’t not wait till the log gets written to the mirror server database, there is a risk of potential data loss in case on any disaster happened to the principal server.

Although below is the default configuration of Database mirroring with principal & mirror server:-

Once our Mirroring is configured with High Safety with Automatic failover, we have another server called witness. Although this servers doesn’t not process any requests to the clients, but it keeps an eye on the Principal server that it’s up & running. It will automatically initiate the failover only if:-

a) Mirror server is connected with witness server after both are disconnected from principal server.

Below is the configuration of Database Mirroring with witness server

This topic covers the basic information about the database mirroring in SQL Server. More Parts on the same topic will be published soon.

.