• Article

Automatic Failover

Automatic failover is supported only in database mirroring sessions running with a witness in high-safety mode (high-safety mode with automatic failover). In high-safety mode with automatic failover, once the database is synchronized, if the principal database becomes unavailable, an automatic failover occurs. An automatic failover causes the mirror server to take over the role of principal server and bring its copy of the database online as the principal database. Requiring that the database be synchronized prevents data loss during failover, because every transaction committed on the principal database is also committed on the mirror database.

For automatic failover to improve reliability, the mirror and principal databases must reside on different computers.

Conditions Required for an Automatic Failover

Automatic failover requires the following conditions:

  • The database mirroring session must be running in high-safety mode and must possess a witness. For more information, see Synchronous Database Mirroring (High-Safety Mode).

  • The mirror database must already be synchronized. This guarantees that all of the log sent to the mirror server has been written to disk.

  • The principal server has lost communication with the rest of the database mirroring configuration, while the mirror and witness retain quorum. If all server instances lose communication, however, and the witness and the mirror server later regain communication, automatic failover does not occur.

    Note

    For more information, see Quorum: How a Witness Affects Database Availability.

  • The mirror server has detected the loss of the principal server.

    How the mirror server detects a failure of the principal server depends on whether it is a hard or soft failure. For more information, see Possible Failures During Database Mirroring.

How Automatic Failover Works

Under the preceding conditions, automatic failover initiates the following sequence of actions:

  1. If the principal server is still running, it changes the state of the principal database to DISCONNECTED and disconnects all clients from the principal database.

  2. The witness and mirror servers register that the principal server is unavailable.

  3. If any log is waiting in the redo queue, the mirror server finishes rolling forward the mirror database.

    Note

    The amount of time required to apply the log depends on the speed of the system, the recent work load, and the amount of log in the redo queue.

  4. The former mirror database moves online as the new principal database, and recovery cleans up all uncommitted transactions by rolling them back as quickly as possible. Locks isolate those transactions.

  5. When the former principal server rejoins the session, it recognizes that its failover partner now owns the principal role. The former principal server takes on the role of mirror, making its database the mirror database. The new mirror server synchronizes the new mirror database with the principal database as quickly as possible. As soon as the new mirror server has resynchronized the databases, failover is again possible, but in the reverse direction.

The following illustration shows a single instance of automatic failover.

Automatic failover

Initially, all three servers are connected (the session has full quorum). Partner_A is the principal server and Partner_B is the mirror server. Partner_A (or the principal database on Partner_A) becomes unavailable. The witness and Partner_B both recognize that the principal is no longer available the session retains quorum. Partner_B becomes the principal server and makes its copy of the database available as the new principal database. Eventually, Partner_A reconnects to the session and discovers that Partner_B now owns the principal role. Partner_A then takes on the mirror role.

After failover, clients must reconnect to the current principal database. For more information, see Connecting Clients to a Database Mirroring Session (SQL Server).

Note

Transactions that have been prepared using the Microsoft Distributed Transaction Coordinator but are still not committed when a failover occurs, are considered aborted after the database has failed over.

Disabling Automatic Failover by Using SQL Server Management Studio

To disable automatic failover, open the Database PropertiesMirroring page, and change the operating mode by selecting one of the following options:

To change the operating mode

Disabling Automatic Failover by Using Transact-SQL

At any point in a database mirroring session, the database owner can disable automatic failover by turning off the witness.

To turn off the witness