Synchronous Database Mirroring (High-Safety Mode)

When transaction safety is set to FULL, the database mirroring session runs in high-safety mode and operates synchronously after an initial synchronizing phase. This topic describes the details of database mirroring sessions that are configured for synchronous operation. This topic assumes that you are familiar with the fundamental concepts of database mirroring operations. For more information, see Database Mirroring Sessions.

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 begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible. As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized.

After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. Note the wait for this message increases the latency of the transaction.

The time required for synchronization depends essentially on how far the mirror database was behind the principal database at the start of the session (measured by the number of log records initially received from the principal server), the work load on the principal database, and the speed of the mirror system. After a session is synchronized, the hardened log that has yet to be redone on the mirror database remains in the redo queue. For more information, see Database Mirroring Sessions.

As soon as the mirror database becomes synchronized, the state of both the copies of the database changes to SYNCHRONIZED.

Synchronous operation is maintained in the following manner:

1. On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log.
2. The principal server writes the transaction to the database and, concurrently, sends the log record to the mirror server. The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a rollback.
3. The mirror server hardens the log to disk and returns an acknowledgement to the principal server.
4. On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.

High-safety mode protects your data by requiring the data to be synchronized between two places. All the committed transactions are guaranteed to be written to disk on the mirror server.

High-Safety Mode Without Automatic Failover

The following figure shows the configuration of high-safety mode without automatic failover. The configuration consists of only the two partners.

When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data). If the principal server is lost, the mirror is suspended, but service can be manually forced to the mirror server (with possible data loss). For more information, see Forced Service (with Possible Data Loss).

High-Safety Mode with Automatic Failover

Automatic failover provides high availability by ensuring that the database is still served after the loss of one server. Automatic failover requires that the session possess a third server instance, the witness, which ideally resides on a third computer. The following figure shows the configuration of a high-safety mode session that supports automatic failover.

Unlike the two partners, the witness does not serve the database. The witness simply 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.

When a witness is set, the session requires quorum—a relationship between at least two server instances that allows the database to be made available. For more information, see Quorum: How a Witness Affects Database Availability and Automatic Failover. For more information, see Database Mirroring Witness.

Automatic failover requires the following conditions:

• The database is already synchronized.
• The failure occurs while all three server instances are connected, and the witness and mirror server remain connected.

The loss of a partner has the following effect:

• If the principal server becomes unavailable under the above conditions, automatic failover occurs. The mirror server switches to the role of principal, and it offers its database as the principal database.
• If the principal server becomes unavailable when those conditions are not met, forcing service (with possible data loss) might be possible. For more information, see Forced Service (with Possible Data Loss).
• If the only mirror server becomes unavailable, the principal and witness continue.

If the session loses its witness, quorum requires both partners. If either partner loses quorum, both partners lose quorum, and the database becomes unavailable until quorum is re-established. This quorum requirement makes sure that in the absence of a witness the database never runs exposed, that is without being mirrored.

See Also

Concepts

Database Mirroring Sessions
Quorum: How a Witness Affects Database Availability
Transact-SQL Settings and Database Mirroring Operating Modes

Other Resources

Monitoring Database Mirroring

Help and Information

Getting SQL Server 2005 Assistance