2.6 Database Mirroring

Database mirroring is a solution that increase the availability of SQL Server database, it is implemented on per-database basis and works only with database that use the Full Recovery model.

Database Mirroring maintains 2 copies of a single database, they must be resided in different Server instance of SQL Server Database Engine, and in different locations.  With database mirroring, it initiates a relationship known as “database mirroring session” between the Server instances.  One Server instance would serve the role of Principal Server, while the other Server instance as “hot” or “warm” standby “Mirror Server”.  The Mirror Server provide “read-only” access to database users.

When a “database mirroring session” is synchronized, the failover to hot standby Server took place without data loss occurred.  If the session is not synchronized, there is possibility of data lose when it failover to a warm standby Server.

Database Mirroring works by redoing every insert, updates and delete operations that occurs in principal server database onto mirror server database as quick as possible by sending a compassed stream of active transaction log records to the mirror server.  Unlike database replication, which works on logical level, database mirroring works at the level of physical log record.

Operation Modes

Database mirroring session runs in either synchronous or asynchronous operation mode.

•Synchronous operation: Transaction is committed on both databases, which increase the transaction latency.

Synchronous operation use “high-safety-mode” for data transaction.  When the session starts, the mirror server synchronise the mirror database together with the principal database as quick as possible and commit the transaction on both databases as soon as data are synchronized.

NB: High-safety-mode with automatic failover requires a third server known as “Witness Server”.  The witness server does not serve the database, it simply support automatic failover by verifying if the principal server is up and functioning.  The mirror server would initiates automatic failover only if the mirror server and witness server remain connected to each other.

•Asynchronous operation: It maximizes the performance by commit transaction without waiting for the mirror server to write the log to disk.  

Asynchronous operation use “high-performance-mode” for data transaction.  As soon as the principal server sends a log record to the mirror server it also sends confirmation to database user without waiting for the mirror server to commit and write the log to disk.

Database Failover Mode

1.Automatic Failover
This failover mode requires high-safety-mode and the presence of mirror server and witness server.  Before initiation, the database must already be synchronized and the witness server must be connected to the mirror server in order for mirror server to initiate the failover.

2.Manual Failover
This failover mode requires high-safety-mode, both databases must be connected to each other and database must already be synchronized.

3.Forces Service

With “high-safety-mode” and “high-performance-mode” without automatic failover, the failover took is possible if the principal server has fails and the mirror server is available to resume the principal server role.

The Benefits of Database Mirroring

1.Increases database availability

In the event of disaster, in “high-safety-mode” with automatic failover, the failover can be accomplished within short period of time without data loss.

2.Increase data integrity

Database mirroring provide complete redundancy of data in database with the “high-safety-mode” or “high-performance-mode” operation.  When the mirror server is unable to read a page it automatically request for a fresh copy of data from principal server.

3.Minimize downtime during server upgrades

With database mirroring, you can sequentially upgrade the instances of SQL Server that are hosting the failover database, this would ensure that the Production database is always available for user access.