A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.
There are two mirroring operating modes. One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded.
In high-performance mode, as soon as the principal server sends a log record to the mirror server, the principal server sends a confirmation to the client. It does not wait for an acknowledgement from the mirror server. This means that transactions commit without waiting for the mirror server to write the log to disk. Such asynchronous operation enables the principal server to run with minimum transaction latency, at the potential risk of some data loss.
All database mirroring sessions support only one principal server and one mirror server. This configuration is shown in the following illustration.
High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.
The following illustration shows a configuration that includes a witness.
For more information, see "Role Switching," later in this topic.
Transaction Safety and Operating Modes
Whether an operating mode is asynchronous or synchronous depends on the transaction safety setting. If you exclusively use SQL Server Management Studio to configure database mirroring, transaction safety settings are configured automatically when you select the operation mode.
If you use Transact-SQL to configure database mirroring, you must understand how to set transaction safety. Transaction safety is controlled by the SAFETY property of the ALTER DATABASE statement. On a database that is being mirrored, SAFETY is either FULL or OFF.
-
If the SAFETY option is set to FULL, database mirroring operation is synchronous, after the initial synchronizing phase. If a witness is set in high-safety mode, the session supports automatic failover.
-
If the SAFETY option is set to OFF, database mirroring operation is asynchronous. The session runs in high-performance mode, and the WITNESS option should also be OFF.
For more information, see Transact-SQL Settings and Database Mirroring Operating Modes.
Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server. In role switching, the mirror server acts as the failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly.
The following three forms of role switching exist.
-
Automatic failover
This requires high-safety mode and the presence of the mirror server and a witness. The database must already be synchronized, and the witness must be connected to the mirror server.
The role of the witness is to verify whether a given partner server is up and functioning. If the mirror server loses its connection to the principal server but the witness is still connected to the principal server, the mirror server does not initiate a failover. For more information, see Database Mirroring Witness.
-
Manual failover
This requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized.
-
Forced service (with possible data loss)
Under high-performance mode and high-safety mode without automatic failover, forcing service is possible if the principal server has failed and the mirror server is available.
.gif) Important: |
|---|
|
High-performance mode is intended to run without a witness. But if a witness exists, forcing service requires that the witness is connected to the mirror server.
|
In any role-switching scenario, as soon as the new principal database comes online, the client applications can recover quickly by reconnecting to the database.