This year at TechEd there's been even more interest in database mirroring than last year - Kimberly's Always-On Demo-Fest yesterday had a whole bunch of questions during the first demo on mirroring and 50% of the booth questions I've answered have been on it too. It seems that more people are getting to the stage of actually implementing SQL Server 2005 this year, which kind of makes sense now that we've put out two Service Packs and companies have had a chance to implement and test new application designs and thoroughly test out workloads on the new version.
There were two really interesting questions that came up that I want to discuss in this post.
Clustering, mirroring and altering the partner timeout
The first question came up during Kimberly's session and is about combining failover clustering and database mirroring. A customer wanted to put the instance hosting the principal database on a failover cluster and have the mirror database on an unclustered server. The issue he saw with the setup was when something happened on the active cluster server and it failed over. The failover would take 60-90 seconds, which means the principal database would not be available during that time.
The problem stems from the way database mirroring figures out whether to initiate a failover. The mechanism it uses is to have all SQL Server instances within a mirroring session send out 'pings' on each of its open mirroring connections every second. If an instance does not receive a ping from one of the other instances in the session within a defined time-out period (called the partner time-out), it considers the other instance as unavailable and takes the appropriate action. The default time-out period is ten seconds.
In the case above, when the cluster is failing over and the principal instance is unavailable for 60-90 seconds, the other instances in the mirroring session will time-out after ten seconds and initiate a mirroring failover. Although this is the correct behavior of mirroring, it is undesirable behavior for this customer. The solution to the problem is to change the partner time-out value - a facility that isn't well known.
To change the partner time-out value for a mirroring session, use the following code:
The value at the end of the statement is the new partner timeout value in seconds. There are a couple of things to be aware of if you're going to do this:
Database mirroring failover types
The second question came up during our VLDB maintenance Q&A later in the afternoon and is about the different kinds of failures that can trigger mirroring failovers, and how quickly the failover happens after the problem occurs. As with most questions I get, I can use my favorite answer of "It depends!" :-) Let's look at some examples of failures and see how quickly the failover occurs, in decreeasing order of speed.
So, don't assume that just because mirroring is setup that every failure will trigger a fast failover.
Hopefully there will be more questions during the rest of the week that I'll get time to blog about!
Quite useful contents
Thanks for this - hopefully this will help a timeout issue with an unstable link.
Thanks Paul for the info.
Which TSQL command will be used for ping purpose in DB Mirroring? Like "select @@servername" to check the stats if a SQL Server instance in failover clustering.
Indeed helpfull, i was looking for it only to fix one problem...thanks.
Browse about SQL Log