Fail Over techniques for Database Mirroring using SQL SERVER 2008, Manual

Fail-over techniques in database mirroring. There are different techniques for database mirroring.

1. Automatic fail over
2. Manual fail over
3. Forced fail over

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

http://msdn.microsoft.com/en-us/library/ms186348.aspx

During a manual failover, the principal and mirror server roles are swapped for the database on which the failover occurs. The mirror database becomes the principal database and the principal database becomes the mirror. For example, the following table shows the how a manual failover swaps the roles of two mirroring partners: System

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.

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.

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.

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.

Manual fail-Over through Query

http://msdn.microsoft.com/en-us/library/ms179481.aspx

When the mirrored database is synchronized, the database owner can initiate manual failover to the mirror server. Manual failover can be initiated only from the principal server.

1.       Connect to the principal server.

2.       Set the database context to the master database:

3.       Issue the following statement on the principal server:

This initiates an immediate transition of the mirror server to the principal role.