February 9, 2011 at 8:37 am
Hi All
We currently have a SQL 2005 Active/Passive cluster that hosts around 70 databases of varying sizes and importance. We would like to have hot standbys for 6 of those databases (for DR purposes) and so we're planning to mirror them to a separate, non-clustered server, utilising a witness server.
My question: is upping the mirroring partner timeout value a sound enough method to avoid conflicts in the cluster and mirror failovers, or should we be re-thinking our plan?
Thanks very much!
February 9, 2011 at 2:41 pm
That timeout parameter is tricky.
It depends on the time it takes for your cluster to failover in order for your witness to keep on mirroring once the passive kicks in. You will probably use high safety mode without automatic failover and set transaction safety to full.
February 9, 2011 at 3:49 pm
From one of the Microsoft sessions which i recently attended, i was told that Microsoft doesn't recommend more than 30 databases per instance to be setup for mirroring.
February 10, 2011 at 2:53 am
Thanks for your replies.
AlexSQLForums (2/9/2011)
That timeout parameter is tricky.It depends on the time it takes for your cluster to failover in order for your witness to keep on mirroring once the passive kicks in. You will probably use high safety mode without automatic failover and set transaction safety to full.
OK, so you're saying it'd be safer to not use automatic failover?
sqldba_icon (2/9/2011)
From one of the Microsoft sessions which i recently attended, i was told that Microsoft doesn't recommend more than 30 databases per instance to be setup for mirroring.
That's fine - we are only mirroring 6 of the 70 databases.
February 15, 2011 at 2:28 pm
OK, so you're saying it'd be safer to not use automatic failover?
Exactly
Be careful setting the timeout!!!
Lets say you set the timeout to 3 minutes and your cluster does not complete the failover under 3 minutes then database mirroring will try to failover to the standby server.
You don't need to failover to standby server because you just need a standby server in case you completely lose your cluster so use high safety mode and set transaction safety to full.
February 16, 2011 at 7:31 am
1) you are into some VERY complex stuff here that is CRITICAL to both high availability AND disaster recovery. you REALLY REALLY should get a qualified consultant on board to help you a) figure out what your company really needs for both of those topics and then b) help you get it set up, teach you how to monitor and troubleshoot and maintain.
2) Books Online has pretty good discussions about mixing clustering, mirroring and log shipping, as do some books such as the one from Allan Hirt.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 4, 2011 at 6:21 am
Hi All
Sorry to drag this one up from the depths again, but I seem to have run into a problem...
After this post, I set up a single test mirrored database on our cluster as follows:
Principal server: Active/Passive cluster
Mirror server: separate server with a single SQL instance
Witness server: separate server with a single SQL instance
I configured a High Safety with Automatic Failover (synchronous) mirror session between the above servers. I could then successfully, failover the principal/mirror databases backwards and forwards between servers without any problems.
I also set the PARTNER TIMEOUT value to 600 secs (10 mins) to avoid the mirror failover kicking in during manual failover of the principal cluster during weekly patching, etc.
However, during the very first principal cluster patching session, the database mirror failed over from the principal to the mirror server! I've spoken to our server guys and the cluster definately failed over within a few mins (well under the 10 min timeout value).
The log messages I can find from around the time of the cluster failover are as follows:
Principal server
07:26:03 [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...
07:26:05 [098] SQLServerAgent terminated (normally)
...numerous logon errors...
Mirror server
No messages
Witness server
07:26:10 Error: 1474, Severity: 16, State: 1.
07:26:10 Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://<principal servername>
07:26:10 Database mirroring connection error 4 'An error occurred while receiving data: '10053(error not found)'.' for 'TCP://<princpal servername>
07:26:10
It looks like the witness server lost sight of the principal cluster when it was failed over and then initiated the mirror failover straight away...but I'm having trouble confirming this. If so, I'm not sure why the 600sec PARTNER TIMEOUT parameter was ignored (I have since checked that it is still set to 600).
Any ideas?
Many thanks 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply