Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Adjusting the automatic failover time for SQL Server Database Mirroring


By:   |   Last Updated: 2008-10-13   |   Comments (7)   |   Related Tips: More > Database Mirroring

Problem

Database Mirroring is becoming a popular feature of SQL Server 2005.  By following a few simple steps either by using the SQL Server Management Studio or by running a few T-SQL commands you can easily establish database mirroring for one or more of your databases.  One of the configuration options for Database Mirroring is the High Availability mode.  With this option three servers are put in place the principal, mirror and witness servers.  This is the only option that allows for automatic failover.  One of the things that I have noticed is that when there are periodic network issues a failover occurs even though there are no issues with the Principal server.  Are there any options to delay the failover,  I did not see anything in SQL Server Management Studio?

Solution

As mentioned above it is pretty simple and straightforward to setup Database Mirroring in the High Availability mode either by using SQL Server Management Studio or by issuing a few T-SQL commands.  If you do a little digging in SQL Server Books online you will notice that some of the options that are documented are not available in the GUI.  One of these options is the PARTNER TIMEOUT.

The PARTNER TIMEOUT is the value that SQL Server uses to determine the maximum period of time an instance waits to get a "ping" message from another instance of SQL Server before determining whether a failover should occur.

With the High Availability mode of Database Mirroring there are three servers that are involved as shown below:

direction of data flow
(source: SQL Server 2005 Books Online)

The three servers constantly ping each other, forming a quorum, and if one of the machines is not available the other machines determine how to handle the failover.  Depending on where the machines are located and the reliability of your network it is possible that the Principal server may be disconnected and the Witness server and the Mirror server still have quorum and therefore initiate the failover.

If there are potential issues with network load or some other reason that may be causing a delay in communicating with all three servers one solution is to change the PARTNER TIMEOUT.  By  default this value is set to 10 seconds, so if a "ping" is not received in this 10 second period a failover may occur.

To make this change to a longer value, such as 20 seconds, the following command would be issued on the Principal server for the database that is mirrored.

ALTER DATABASE dbName SET PARTNER TIMEOUT 20

Be careful on the value that use for this option.  If this value is set to high and a failure really does occur, the automatic failover will take longer based on the value you set.

In addition, the lowest this value can be set to is 5 seconds based on information found in SQL Server Books Online.

Next Steps
  • If you are using the High Availability mode of Database Mirroring and are experiencing failovers look into this option to see if this will help
  • Knowing what to change this value to is the tricky part. As mentioned you do not want the value too high, because this could delay the failover if there really is an issue.  Since Microsoft started with 10 seconds it may make sense to adjust this to 20 or 30 seconds to see if this solves your problem..
  • Here are some additional Database Mirroring tips


Last Updated: 2008-10-13


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, August 17, 2015 - 11:27:12 PM - SQLSyd Back To Top

Thanks Greg !


Thursday, July 17, 2014 - 7:15:14 PM - brahmaiah Back To Top

 

 

HI Team,

 

I am new to the SQL. We are planning to set up a Prodution envinronment by next week.

Can some give me the sugessions which are taks i have to perform.

Thanks 

Brahmaiah.B


Thursday, March 13, 2014 - 9:34:37 AM - Greg Robidoux Back To Top

Hi Mas,

To make this change to a longer value, such as 20 seconds, the following command would be issued on the Principal server for the database that is mirrored.


Thursday, March 13, 2014 - 4:09:01 AM - Mas Back To Top

Sir,

I have a little confusion. Please clarify me. I just want to know where do we need to run the above query.

Do we need to run in Principal server only or Both Principal and mirror server.

If we run the above command in pricipal server alone, will that reflect in Mirror server also.

 

Thanks,

Ameen


Tuesday, February 26, 2013 - 4:33:16 PM - Greg Robidoux Back To Top

@Patel - there is a limit to how many databases you can Mirror.  Take a look at these articles:

http://support.microsoft.com/kb/2001270

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx

For Log Shipping you don't have that same issue because the copying of the transaction log backups and restoring them to your standby can be done totally on the standby server.  You may want to create your own Log Shipping process for that many databases instead of using the built-in functionality.  It may make it easier to setup and maintain.  At some point you will hit a limit based on how many backups are occuring, the time to copy the backups and the time to restore, but a lot of this depends on how much activity is occuring on the boxes and how much processing power (io, memory, cpu) there is on the boxes.


Tuesday, February 26, 2013 - 4:13:29 PM - Patel Back To Top

Greg,

I want to know how many database we can configure log shipping (There is any limit to configure log shipping or Mirroring on single Instance) we running 2 instance on single server with 125 databases each instance in production and I want to create standby server. Please advice me.


Tuesday, February 19, 2013 - 6:20:20 PM - Duncan Back To Top

This is a very informative article, I have been studying and came upon this vital piece of information.

 

Thanks a million to Greg


Learn more about SQL Server tools