September 7, 2011 at 5:15 pm
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?
September 7, 2011 at 7:09 pm
What command are you using to change the timeout?
What does this return when run against the principal
select db_name(database_id),
mirroring_connection_timeout
From sys.database_mirroring
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 8, 2011 at 10:50 am
ALTER DATABASE admin SET PARTNER TIMEOUT 59
I use this to check the various values of my principle/mirror:
SELECT DB_NAME(DATABASE_ID) DBName, @@SERVERNAME CurrentServer, mirroring_state_desc, mirroring_role_desc,
mirroring_safety_level_desc, mirroring_partner_name, mirroring_witness_name,
mirroring_witness_state_desc, mirroring_connection_timeout
FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL
mirroring_connection_timeout = 59
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply