Overview of Database Mirroring

  • Article

Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. Database mirroring works with any supported database compatibility level.

Note

You cannot mirror the master, msdb, tempdb, or model databases.

Database mirroring maintains two copies of a single database that must reside on different instances of SQL Server Database Engine (server instances). Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server), while the other server instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover with no loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

Benefits of Database Mirroring

Database mirroring is a simple strategy that offers the following benefits:

  • Increases data protection.
    Database mirroring provides complete or nearly complete redundancy of the data, depending on whether the operating mode is high-safety mode or high-performance mode. For more information, see "Operating Modes," later in this topic.
  • Increases availability of a database.
    In the event of a disaster, in high-safety mode with automatic failover, failover quickly brings the standby copy of the database online (with no data loss). In the other operating modes, the database administrator has the alternative of forcing service (with possible data loss) to the standby copy of the database. For more information, see "Role Switching," later in this topic.
  • Improves the availability of the production database during upgrades.
    To minimize downtime for a mirrored database, you can sequentially upgrade the instances of SQL Server that are participating in a database mirroring session, incurring the downtime of only a single failover. This form of upgrade is known as a rolling upgrade. For more information, see How to: Install a Service Pack on a System with Minimal Downtime for Mirrored Databases.

How Database Mirroring Works

The principal and mirror servers communicate and cooperate as partners within a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending every active transaction log record to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record.

Operating Modes

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 committed 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 begins, the mirror server synchronizes the mirror database with the principal database as quickly as possible. Once the databases are synchronized a committed 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 attempts to keep up with the log records sent by the principal server. 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.

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, without waiting 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 permits the principal server to run with minimum transaction latency, at the risk of some potential data loss.

All database mirroring sessions support only one principal server and one mirror server. The following figure illustrates this configuration.

Partners in a database mirroring session

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 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.

The following figure shows a configuration that includes a witness.

A mirroring session 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 need to 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.

Role Switching

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.

    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, once the new principal database comes online, the client applications can recover quickly by reconnecting to the database.

Database Mirroring Support

Database mirroring partners and witnesses are supported by SQL Server 2005 Standard Edition SP1 and later versions, and by SQL Server 2005 Enterprise Edition SP1 and later versions. But the partners must use the same edition, and asynchronous database mirroring (high-performance mode) is supported only by SQL Server 2005 Enterprise Edition SP1 and later versions. Witnesses are also supported by SQL Server 2005 Workgroup Edition SP1 and later versions and by SQL Server 2005 Express Edition SP1 and later versions.

Associated Database Mirroring Topics

See Also

Other Resources

Connecting Clients to a Mirrored Database
Database Mirroring and Other Features and Components
sp_dbcmptlevel (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance