Mirroring: What is mirroring – Architecture, Prerequisites, Operating modes

The Database Mirroring in SQL Server was firstly implemented on Microsoft SQL Server 2005, which is basically designed for high-performance and high-availability resolution of database redundancy. 

What is mirroring ?

SQL Server database mirroring is a disaster recovery and high availability technique which
involves two SQL Server instances on the same or different machines. Mirroring is implemented on a database basis with only full recovery model.

Database mirroring maintains two copies of a single database on different server instances of SQL Server Database Engine.

Servers in Mirroring:

Mirroring contains principal,mirror and witness server.

Mirror server :

In a database mirroring configuration, the server instance on which the mirror database resides.

Principal server :

In database mirroring, the partner whose database is currently the principal  database.

Witness :

For use only with high-safety mode, an optional instance of SQL Server that enables the mirror server to recognize when to initiate an automatic failover. Unlike the two failover partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.

Operating modes:-

SQL Server database mirroring can be set to provide high availability or disaster recovery.
Depending on the needs, a DBA can choose among three available modes.

High safety :

Data is written and committed on the principal and mirror databases synchronously. Only after committing on both databases, the database application can continue with activity.

1. Might produce delay and slower operation because transactions must be committed on both databases.

1. If the principal database goes down, two options are available:-

• Do nothing: – wait for the principal to become available again. During that time, the SQL Server instance is unavailable. Mirroring will continue where it has stopped.
• Force the SQL Server instance on the mirror database :– the mirror database becomes the principal. Possible data loss due to committed transactions on the original principal database which are not yet committed on the mirror currently acting as the principal.

High safety with automatic failover:

Three servers are necessary. Data is written and must be committed synchronously both on the principal and mirror databases. Only after committing on both databases, the application can continue running.

1. Might produce delay and slower operation because transactions must be committed on both databases.
2. If the principal database goes down, only one option is available:

• Let the automatic failover process complete, the mirrored database becomes the principal.

High performance: 

The asynchronous communication, data is written and committed on the principal server, and later sent and committed to the mirror server. Automatic failover isn’t possible and the witness server can’t be used.

1. The high performance mode is only available in the Enterprise edition of SQL Server.
2. If the principal database goes down, three options are available:

• Do nothing :– wait for the principal to become available again. The SQL Server is unavailable. Mirroring will continue where it has stopped.
• Force the SQL Server instance on the mirror database: – the mirror database becomes the principal. Greater possibility for data loss, due to asynchronous communication between databases.
• Manual update :– to reduce data loss, take the tail of the log backup if the failed server allows, remove mirroring and restore the tail of the log on the previously mirrored database.

Prerequisites:

• For a mirroring, the partners and the witness, if any, must be running on the same version of SQL Server.
• The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring.
• The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring.
• Verify that the mirror server has sufficient disk space for the mirror database.
• When you are creating the mirror database on the mirror server then restore the backup of the principal database with same database name WITH NORECOVERY. Also, all log backups that were created after that backup was taken must also be applied, again WITH NORECOVERY.
• All of the server instances in a mirroring session should use the same master code page and collation. Differences can cause a problem during mirroring setup.
• Database  mirroring should be configure with domain login
• The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups. Prior to configuring mirroring ensure that at least 1 Tran log is restored in addition to full backup with NORECOVERY mode.
• The mirror database must have the same name as the principal database.