How does mirroring work in sql server

A server instance can be a partner in some sessions and a witness in other sessions. The mirror server instance must be running the same edition of SQL Server. 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.

Establishing a new mirroring session or adding a witness to an existing mirroring configuration requires that all involved server instances run the same version of SQL Server. However, when you are upgrading to SQL Server or a later version, the versions of the involved instances can vary. 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. If a witness is set in high-safety mode, the session supports automatic failover. For more information, see 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.

If the principal database is lost but the principal server is still running, immediately attempt to back up the tail of the log on the principal database. If the tail-log backup succeeds, removing mirroring may be your best alternative. After removing mirroring, you can restore the log onto the former mirror database, which preserves all of the data.

If the tail-log backup failed and you cannot wait for the principal server to recover, consider forcing service, which has the advantage of maintaining the session state. Forced service is strictly a disaster recovery method and should be used sparingly. Forcing service is possible only if the principal server is down, the session is asynchronous transaction safety is set to OFF , and either the session does not have any witness the WITNESS property is set to OFF or the witness is connected to the mirror server that is, they have quorum.

Forcing service causes the mirror server to assume the role of principal and serve its copy of the database for clients. When service is forced, whatever transaction logs the principal has not yet sent to the mirror server are lost.

Therefore, you should limit forced service to situations where possible data loss is acceptable and immediate database availability is critical. This section describes how synchronous database mirroring works, including the alternative high-safety modes with automatic failover and without automatic failover , and contains information about the role of the witness in automatic failover.

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 section describes the details of database mirroring sessions that are configured for synchronous operation. 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. To monitor state changes in a database mirroring session, use the Database Mirroring State Change event class. 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. On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log. 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.

The mirror server hardens the log to disk and returns an acknowledgement to the principal server. 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 with Automatic Failover. The following figure shows the configuration of high-safety mode without automatic failover. It is limited to only two Servers 1. In this we can log ship to multiple Servers 2. Mirroring with a Witness Server allows for High Availability and automatic fail over.

Log shipping is only as current as how often the job runs. If we ship logs every 15 minutes, the secondary server could be as far as 15 minutes. Making it more of a Warm Standby. We can configure our DSN string to have both mirrored servers in it so that when they switch we notice nothing. We can leave the database in read only mode while it is being updated.

Good for reporting servers. While mirrored, our Mirrored Database cannot be accessed. Good for disaster recovery 5. Mirroring with SQL Server standard edition is not good for load balancing Things that are explained in this article Roles Of the Server Modes of Database Mirroring Loss of Servers Prerequisites Restrictions Endpoints Creating a mirror database for mirroring Database Mirroring Roles of the Server in Database Mirroring Principal server The principal server hosts the active copy of the database referred to as the principal database and services client requests.

SQL Server database mirroring can be set to provide high availability or disaster recovery. Depending on the needs, a DBA can choose among three available modes. Using SQL Server database mirroring has multiple benefits: a built-in SQL Server feature, relatively easy to set up, can provide automatic failover in high safety mode, etc. Database mirroring can be combined with other disaster recovery options such as clustering, log shipping, and replication.