Database mirroring adjust automatic failover time

Problem

Database mirroring has become a popular feature in SQL Server 2005. With the following simple steps, using the SQL Server Management suite or running some T-SQL commands, you can easily create database mirroring in one or more of your databases. One of the configuration options for database mirroring is high availability mode. With this option, the principal, mirror, and witness three servers are placed in the appropriate location. This is the only option that allows automatic troubleshooting of failover capabilities. One thing I've noticed is that when there are recurring network problems, a failover can occur even if the principal server has no problems. Are there any options to delay this failover? Because I don't see anything in the SQL Server Management suite.

Expert answers

As mentioned above, it is straightforward to establish database mirroring in the high-availability mode options by using the SQL Server Management suite or by executing some T-SQL commands. If you do some digging for SQL Server online Help, you'll notice that some of the options that are documented are not available in the GUI. A member of these options is partner TIMEOUT.

PARTNER timeout is the value that SQL Server uses to determine the maximum period of time when an instance waits for a "ping" message to be sent from another instance of SQL Server before confirming that a failover should occur.

With the high availability pattern of database mirroring, the relationships of these three servers are as follows:

The three servers continuously ping each other to form a retention quorum, and if one of the three servers is not available, the other servers will determine how to troubleshoot the failover. Given the location of these machines and the reliability of the network, the principal server will be disconnected, the witness and mirror servers will remain arbitrated, and because of this, both servers can initialize failover.

If there are problems, such as Network load or other reasons that cause communication between these three servers to be delayed, then one solution is to change partner TIMEOUT. By default, this value is set to 10 seconds, so if a "ping" is not received within a period of 10 seconds, a failover will occur.

To change this to a longer value, such as 20 seconds, the following command should be executed in the principal server database to be mirrored.

ALTER DATABASE dbname SET PARTNER TIMEOUT 20

Be aware of the values used for this option. If this value is set to high and the failure does occur, automatic failover will consume more time based on the value you set.

In addition, the "lowest" value can be set to 5 seconds according to the information provided in the SQL Server online Help.