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.
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.
.gif "Planned manual failover")
Manual failover requires transaction safety to be set to FULL and the database to be in the SYNCHRONIZED state.
Manual failover initiates the following sequence of actions:
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.
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).
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).
The mirror server becomes the new principal server, and the former principal server becomes the new mirror server.
The new principal server rolls back any uncommitted transactions and brings its copy of the database online as the principal database.
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 Mirrored Database.
To initiate manual failover
- How to: Manually Fail Over a Database Mirroring Session (SQL Server Management Studio)
- How to: Manually Fail Over a Database Mirroring Session (Transact-SQL).
See Also
Concepts
Database Mirroring Sessions
Mirroring States
Estimating the Interruption of Service During Role Switching
Possible Failures During Database Mirroring
Synchronous Database Mirroring (High-Safety Mode)