Database mirroring is a configuration where two or three database servers are located, run on separate computers, and cooperate to keep copies of the database and log file of transactions.
Both the primary server and the mirror server keep a copy of the database and the transaction log, while the third server, called a witness, is used when it is necessary to determine which one of the other two servers can have database properties. The witness does not maintain a copy of the database. The configuration of the three servers of the database is called Mirror System, and the primary server and mirror together are the Operational Servers or Partners.
Database Mirroring in SQL Servers
SQL Server provides two operating modes for database mirroring.
High-Safety Mode
The database mirroring session operates in a high-safety mode when transaction safety is set to FULL and runs synchronously following an initial synchronizing phase.
In order to achieve synchronous operation for a session, the mirror server must synchronize the mirror database with the principal database. When the session begins, the principal server starts sending its active log to the mirror server. Then, the mirror server writes all of the incoming log records to disk as quickly as possible. Once the received log records have been written to disk, the databases are synchronized, and they will remain so as long as the partners remain in communication.
After the synchronization finishes, every transaction completed on the principal database is also performed on the mirror server, assuring data protection. This is accomplished by waiting to do a transaction on the principal database until it gets a message from the mirror server affirming that it has hardened the transaction's log to disk.
The time required for synchronization depends mostly on how far behind the mirror database was at the start of the session, the workload on the principal database, and the speed of the mirror system. Once a session is synchronized, the hardened log that has yet to be redone on the mirror database remains in the redo queue.
High-Performance Mode
When transaction safety is OFF, the database mirroring session operates asynchronously, supporting only high-performance mode. This mode improves performance but has an impact on high availability. High-performance mode uses both the principal server and the mirror server. Problems on the mirror server do not affect the principal server. If the principal server is lost, the mirror database is marked DISCONNECTED, but it is still available as a warm standby.
High-performance mode supports only forced service, which uses the mirror server as a warm standby server. Forced service is a possible response to the failure of the principal server. As data loss is possible, other alternatives should be taken into account before forcing service to the mirror.
Advantages And Disadvantages Of Using SQL Server Database Mirroring
Using SQL Server database mirroring has multiple benefits: a built-in SQL Server feature, relatively easy to configure, can provide automatic failover in high-protection mode. The database mirroring can be combined with other disaster recovery options such as grouping, logging, and replication. However, the most important advantage of this method is that it allows automatic failover without human intervention (provided that a third witness is installed).
The disadvantage of the mirror, regarding log shipping and replication, is that we can only have a secondary or mirror machine that is not accessible and we cannot have it in reading mode.
Database mirroring will be removed from SQL Server in future versions in favor of AlwaysOn Availability Groups. Also, database mirroring is a solution only for each database, which means that logins and tasks from the main SQL Server must be manually recreated in the mirror. There is also a possibility for a delay, which can only be reduced with better hardware.
Contact Soaring Eagle Consulting for a Free Database Evaluation Today
Getting started is simple. Click the button below to request your free one-hour database assessment from the DBA experts at Soaring Eagle Consulting®.
