How to perform SQL Server Mirroring Manual Failover?

Leave a comment (6) Go to comments

What is SQL Server Mirroring Failover ?

When the principal server is offline, the mirror and the witness will form a quorum and promote the mirror to be the principal. This is known as Mirroring Failover.

Failover can be either automatic or manual.

  • Automatic Failover: If the principal server fails in High Availability mode, failover is automatic. When the principal server is offline, the mirror and the witness will form a quorum and promote the mirror to be the principal. If the original principal comes back online, it will become the mirror and this all is being done automatically.
  • Manual Failover: If the principal server fails in High Protection mode, you must initiate manual failover to make mirror server as a Principal (Primary) Server.

Note :

  1. If the original principal comes back online, it will become the mirror.
  2. You may use manual failover even if automatic failover is available.

How to initiate SQL Server Mirroring manual Failover ?

Prior to continue on this answer, Please check my previous post, How to Setup Mirroring in SQL Server ?(Screen Shots), where we have describe the business requirement and deployed mirroring with Witness Server.  This answer is continuation with my last post (screen shots),


SQL Server Mirroring manual Failover

To initiate a manual failover, you must initiate manual failover by performing the following steps on the
principal server:

  1. Connect to the principal server instance and, in the Object Explorer pane, click the server name to expand the server tree.
  2. Expand Databases and then select the database to be mirrored.
  3. Right-click the database, and then click Properties. This opens the Database Properties dialog box.
  4. In the Select a page pane, click Mirroring.
  5. Click Failover.

So Let me continue with my example,

Step 1 : Connect to the principal server instance and, in the Object Explorer pane, click the server name to expand the server tree.

I my demo example, .\SQL1 is currently a principal server and .\SQL2 is a mirror Server and .\SQL3 is witness Server. So,

  1. connect to .\SQL1 and will Expand Databases and then
  2. select “Test_SQLServer_Mirroring” to be mirrored.
  3. Right-click the database, and then click Properties

as shown in screen shot

How to perform SQL Server Mirroring Manual Failover? What SQL Server Does when we manually initiate a failover What is SQL Server Mirroring Failover testing SQL Server Mirroring failover manuall test SQL Server Mirroring failover SQL Server Mirroring manual failover SQL Server Mirroring Failover Manually sql server mirroring failover background SQL Server Mirroring failover SQL Server Manual Mirroring Failover How to initiate SQL Server Mirroring manual Failover Failover in SQL Server Mirroring

This opens the Database Properties dialog box.

Step 2 : Initiate Failover from the Database properties box

  1. This opens the Database Properties dialog box.
  2. In the Select a page pane, click Mirroring.
  3. Click Failover as shown in screen shot

as shown in screen shot

How to perform SQL Server Mirroring Manual Failover? What SQL Server Does when we manually initiate a failover What is SQL Server Mirroring Failover testing SQL Server Mirroring failover manuall test SQL Server Mirroring failover SQL Server Mirroring manual failover SQL Server Mirroring Failover Manually sql server mirroring failover background SQL Server Mirroring failover SQL Server Manual Mirroring Failover How to initiate SQL Server Mirroring manual Failover Failover in SQL Server Mirroring

once you click on Failover, a confirmation will reconfirm that “Failing over database mirroring will swap the roles of the mirror and principal databases. The mirror database will become the principal database, and the current principal database will become inaccessible. If you have just modified any properties in the Database Properties dialog box, those changes will be lost. In addition, SQL Server must close all other connections to the current principal database” as shown below

How to perform SQL Server Mirroring Manual Failover? What SQL Server Does when we manually initiate a failover What is SQL Server Mirroring Failover testing SQL Server Mirroring failover manuall test SQL Server Mirroring failover SQL Server Mirroring manual failover SQL Server Mirroring Failover Manually sql server mirroring failover background SQL Server Mirroring failover SQL Server Manual Mirroring Failover How to initiate SQL Server Mirroring manual Failover Failover in SQL Server Mirroring

Click YES, this will take few seconds 5-30 seconds, This will actual initiate a failover and will swap the roles, now we can verify via Database status, as shown below. [Why it took 5-30mseconds, check What SQL Server Does, when we manually initiate a  failover ?]

How to perform SQL Server Mirroring Manual Failover? What SQL Server Does when we manually initiate a failover What is SQL Server Mirroring Failover testing SQL Server Mirroring failover manuall test SQL Server Mirroring failover SQL Server Mirroring manual failover SQL Server Mirroring Failover Manually sql server mirroring failover background SQL Server Mirroring failover SQL Server Manual Mirroring Failover How to initiate SQL Server Mirroring manual Failover Failover in SQL Server Mirroring

As shown above, Now

  • Database named [Test_SQLServer_Mirroring] on Server .\SQL1 is mirror now and
  • Database named [Test_SQLServer_Mirroring] on Server .\SQL2 is principal

We have successfully done the Mirroring Manual failover.

What SQL Server Does, when we manually initiate a  failover ?

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.

For More information, do check my previous posts on Mirroring:

If you liked this post then,

Subscribe to this Blog via Email:

Click here to Subscribe to FREE email updates from “DBATAG “, so that you do not miss out anything on SQL Server !!!

TAGS : SQL Server Mirroring failover,Failover in SQL Server Mirroring, SQL Server Mirroring Failover Manually,SQL Server Manual Mirroring Failover,SQL Server Mirroring failover,test SQL Server Mirroring failover,testing SQL Server Mirroring failover manually,SQL Server Mirroring manual failover

EOF - How to perform SQL Server Mirroring Manual Failover?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

6 Comments.

  1. How do you initiate a manual failover if the principle server is down and you can’t connect to it?

  2. Can I use Express Edition for Witness Server ?

  3. Nice post on mirroring, thanks for making this eeasy for me.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.