5

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?

Improve this question
4
  • So you have two instances installed on the same cluster, both as clustered resources and mirroring between the instances?
    – mrdenny
    Sep 9, 2011 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, 2011 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, 2011 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, 2011 at 17:34

1 Answer 1

Reset to default
2

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...

Improve this answer
1
  • 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, 2011 at 23:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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