Question: What would be the best practice to configure Failover Clustering and Database Mirroring?
This question is frequently asked in one of the technical discussion groups I frequent. My reply follows.
I have a customer that wants to combine Failover Clustering with Database Mirroring in which the last one will be for disaster recovery. In order that the mirroring will act as primary only if the cluster fails, does the mirroring should be configured as manual? How do we configure mirroring in automatic when both nodes in the cluster fail?
My answer:
If you want automatic failover in case all clustering nodes fail, then you should definitely not set it to manual failover only. The best practice is to set the partner timeout period for mirroring to a number large enough to not be triggered by a cluster failover. The default timeout is 10 seconds.
The following command would set it to a timeout of 30 seconds:
Alter Database <Database Name> Set Partner Timeout 30;
Note: this can be set on the principal server only.
The following scripts can be downloaded as text files. You will need to change the file extension to .ps1 in order to execute them. Backup a database Restore a database Scan a server to find a free port Query DNS to get the FQDN of a server
To see some examples of my other forms of writing, please visit my page on WritersCafe.org. It is almost exclusively horror fiction, but I sometimes throw other things in there too from time to time. There's one science fiction story, a couple of poems, and quite a few humor pieces as well.
Look for me in the SQL Q&A section of the August, 2007 issue of TechNet Magazine. August issue of TechNet Magazine's SQL Q&A column
