How Transaction works in mirroring
MSSQLSERVER Mirroring concepts in Hindi|
Operating mode Transaction safety Witness state
High-performance mode OFF NULL (no witness)**
High-safety mode without automatic failover FULL NULL (no witness)
High-safety mode with automatic failover* FULL CONNECTED
High-performance mode
The database mirroring session operates asynchronously and uses only the principal server and mirror server. The only form of role switching is forced service (with possible data loss).
High-safety mode
The database mirroring session operates synchronously and, optionally, uses a witness, as well as the principal server and mirror server.
Transaction safety
A mirroring-specific database property that determines whether a database mirroring session operates synchronously or asynchronously. There are two safety levels: FULL and OFF.
Witness
For use only with high-safety mode, an optional instance of SQL Server that enables the mirror server to recognize whether 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.
Asynchronous Database Mirroring (High-Performance Mode)
This section describes how asynchronous database mirroring works, when it is appropriate to use high-performance mode, and how to respond if the principal server fails.
When transaction safety is set to OFF, the database mirroring session operates asynchronously. Asynchronous operation supports only one operating mode-high-performance mode. This mode enhances performance at the expense of high availability. High-performance mode uses just the principal server and the mirror server. Problems on the mirror server never impact the principal server. On the loss of the principal server, the mirror database is marked DISCONNECTED but is available as a warm standby.
High-performance mode, supports only one form of role switching: forced service (with possible data loss), which uses the mirror server as a warm standby server. Forced service is one of the possible responses to the failure of the principal server. Because data loss is possible, you should consider other alternatives before forcing service to the mirror. For more information, see Responding to Failure of the Principal, later in this topic.
The following figure shows the configuration of a session using high-performance mode.
Partner-only configuration of a session
In high-performance mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. Transactions commit without waiting for the mirror server to write the log to disk. Asynchronous operation permits the principal server to run with minimum transaction latency.
The mirror server attempts to keep up with the log records sent by the principal server. But the mirror database might lag somewhat behind the principal database, though typically the gap between the databases is small. However, the gap can become substantial if the principal server is under a heavy work load or the system of the mirror server is over loaded.
