• Article

Manual Failover

Manual failover disconnects the clients from the database and reverses the roles of the partners. Only high-safety mode supports manual failover.

Note

This topic assumes that you are familiar with high-safety mode. For more information, see Synchronous Database Mirroring (High-Safety Mode).

Maintaining Availability During Upgrades

The database administrator can use manual failover for upgrading hardware or software without sacrificing availability. To use database mirroring for software upgrades, the mirror server and/or system must have already received the upgrades.

Note

Database mirroring should be able to do a rolling upgrade, but this is not guaranteed, because future changes are unknown. For more information, see How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances.

The following figure illustrates an instance of using manual failover to maintain database availability while you upgrade a database server instance. When the upgrade is completed, an administrator may optionally fail over back to the original server instance. This is useful when the administrator wants to stop the mirroring session and use the mirror server elsewhere. In this way, a single server instance can be used repeatedly when updating a series of database server instances.

Planned manual failover

Conditions Required for a Manual Failover

Manual failover requires transaction safety to be set to FULL (that is, high-safety mode). When the partners are connected and the database is already synchronized, manual failover is supported.

How Manual Failover Works

Manual failover initiates the following sequence of actions:

  1. The principal server disconnects clients from the principal database, sends the tail of the log to the mirror server, and, in preparation for switching to the mirror role, sets the mirroring state to SYNCHRONIZING.

  2. The mirror server records the log sequence number (LSN) of the last log record received from the principal as the failover LSN.

    Note

    To view this LSN, select the mirroring_failover_lsn column from sys.database_mirroring (Transact-SQL).

  3. If any log is waiting in the redo queue, the mirror server finishes rolling forward the mirror database. The amount of time required depends on the speed of the system, the recent workload, and the amount of log in the redo queue. For a synchronous operating mode, the failover time can be regulated by limiting the size of the redo queue. However, this can cause the principal server to slow down to allow the mirror server to keep up.

    Note

    To learn the current size of the redo queue, use the Redo Queue performance counter in the database mirroring performance object (for more information, see Monitoring Database Mirroring).

  4. The mirror server becomes the new principal server, and the former principal server becomes the new mirror server.

  5. The new principal server rolls back any uncommitted transactions and brings its copy of the database online as the principal database.

  6. The former principal takes on the mirror role, and the former principal database becomes the mirror database. The new mirror server quickly resynchronizes the new mirror database with the new principal database.

    Note

    As soon as the new mirror server has resynchronized the databases, failover is again possible, but in the reverse direction.

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

To initiate manual failover