SQL Mirroring has a TIMEOUT option, but when and why would you want to use it? First we need to understand what this option does and that is very simple. This option defines the amount of time that has to pass without receiving a ping response from a partner before a failover is initiated. If you are familiar with clustering then think heartbeat (but over a public connection). The partner will be considered disconnected and either a manual failover can be performed or an automatic failover will occur, depending on your configuration.
The other thing we need to know about the TIMEOUT option is that the default is 10 seconds if you do not change it. Books Online tells us if you set it for anything between 0 and 4 seconds it will automatically set the timeout to 5 seconds. If you set it for 5 seconds or greater it will be set to that value.
Network Latency
So why would you want to change this option? The first scenario would be one of a slow network connection or where the partners are geographically dispersed. If your network latency between partners is greater than the defined mirroring session timeout, then a disconnected state might be detected. This could cause a failover event, even though the partner is actually online, all because the pings did not return to the partner in time. If this scenario is true for you then you have to determine how long it takes a packet to get from partner to partner. You can use “tracert”, but I suggest doing a simple “ping” which will return a minimum, maximum, and average response time. To be absolutely certain you pick an appropriate timeout you need to perform the ping from Principal to Mirror, Mirror to Principal, Principal to Witness, Witness to Principal, Mirror to Witness, and Witness to Mirror. Every network is different and you can never guarantee that a packet from Server1 to Server2 will follow the same route as a packet from Server2 to Server1. This is why I suggest performing your ping tests in both directions. Write down the maximum response time from each test and then select the largest maximum as your timeout along with a couple extra seconds of padding to account for times of high network traffic. Remember that ping gives response time in milliseconds, so don’t forget to convert your time to seconds.
Failover Clustering
The other scenario where you will want to change this option is if one of the partners is a failover cluster. In this case you not only need to factor in network latency, but also the amount of time it takes for a node to failover. You don’t want a mirroring failover to occur during a cluster failover so you want to make sure you set a timeout that accounts for network latency plus the time it takes for a node to failover. So how do you determine that? We already discussed how to determine the network latency piece, so now we just need to know how long it takes a node to fail over. Start by failing over the SQL group and look in the cluster logs to see the time the failover started and the time it came back online, and then subtract the times to get the duration. You might be thinking about doing a ping test while you failover the group, but this will not be accurate. A ping response will be returned as soon as the cluster IP resource comes online, but prior to the SQL resources coming online. This is because the SQL resources depend on the disk resources, the disk resources depend on the network name resource, and the network name resource depends on the IP resource. That means the IP resource will come online first and SQL comes online last.
Now that we have our timing ironed out and have settled on an appropriate TIMEOUT setting, we need to actually set it. This MUST be run on the PRINCIPAL.
ALTER DATABASE MyDB SET PARTNER TIMEOUT TimeinSeconds
The last thing you might want to know is how to find out what the current TIMEOUT setting is. Here is how to get it and I highly suggest you explore the other information in this system view.
SELECT Mirroring_Connection_Timeout
FROM sys.database_mirroring
WHERE database_id = db_id('MyDB')
I am trying to sync 2 servers running enterprise edition SQL. The link between the 2 servers was lost and automatically failed over from the A to the B server. Unfortunately were informed only a month after the fail over occurred. If I try to run a sync now it gives me a timeout message. I am looking for the command to run in SQL Manager to be able to extend the sync period so as to give more time to be able to sync the databases.
If they have been out of sync for a month you’re highly unlikely to get them back in sync. Your best bet is to reestablish the mirror with B as the primary. Once the mirror is reestablished you can failover so A becomes the primary.