Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm testing a 2 node windows failover cluster with mirroring in high safety mode and automatic failover. My mirror is a standalone server as is the witness. All instances are 2008 R2 RTM Enterprise Edition (64-bit) (this is a test, the witness will be Express in prod)

Because I'd like to prevent failover to the mirror when I failover one cluster node to the other (or in the case we just lose a node), I've upped the partner timeout value. My cluster fails over fairly quickly, somewhere between 25 and 30 seconds. But, even when I set the value of the timeout to 59 seconds, the mirrored DB still fails over to the mirror server, and rather quickly.

Generally, I'm testing by simply moving the SQL resource from one node to the other in the cluster manager but I did try it by rebooting the active node as well. The same is true when I failback. I can shutdown the SQL Service on my mirror server (which is now the principle) and by querying sys.database_mirroring, I see the mirror (former principal) change state and description in very short order.

So, what am I doing wrong or assuming about how the timeout works?

share|improve this question
    
So you have two instances installed on the same cluster, both as clustered resources and mirroring between the instances? –  mrdenny Sep 9 '11 at 0:48
    
I actually do, but that second instance is not part of this test. For the purpose of testing, I'm mirroring 1 DB of 6 on a default instance housed on a 2 node cluster. The mirror is a separate VM as is the witness. And, I'm struggling to adequately test the loss of a "active" node server without actually going to the data center to pull the plug. –  Sandra Sep 12 '11 at 23:42
    
How long does it take for crash recovery to run on the database you are testing? For mirroring to decide that it doesn't need to fail over the database to the mirror from the primary the instance needs to be up and crash recovery needs to be complete within the timeout setting. –  mrdenny Sep 13 '11 at 19:08
    
I can't give you actual timing but it's fairly quick. ~30 seconds. And, I couldn't keep the mirror from taking over even when the timeout was set to 5 minutes. This seems to be less about the timeout value and more about how I'm testing. Initiating failover or rebooting wasn't good, because SQL went down gracefully while the OS was up and that triggered the failover to the mirror. I need more of an immediate "everything disappears" type test but haven't been able to make it work, as I describe below. –  Sandra Sep 16 '11 at 17:34

1 Answer 1

I'd suggest that the clean shutdown of the principal is causing it.

From Automatic Failover

How Automatic Failover Works

  1. If the principal server is still running, it changes the state of the principal database to DISCONNECTED and disconnects all clients from the principal database.

One may assume that the principal says "I'm DISCONNECTED" on clean shutdown

To test this correctly, remove the network cables (or power leads) from the node hosting the principal to simulate/invoke a "real" server failure

I assume that the mirror is actually remote and not on the same cluster...

share|improve this answer
    
This is what I've come to understand as well. A reboot or failover is too graceful and with a failover, windows is still around to communicate that SQL is down. So, the mirror takes over. So, we tested a couple of other methods today: power off via the DRAC, disabling the LAN interface. Still, no luck. It doesn't look like I can get a true test without sending someone to the data center to pull a plug. (And, you're correct. The mirror is not on the cluster. It's a separatae VM now, physical when we go to production.) –  Sandra Sep 12 '11 at 23:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.