August 15, 2012 at 2:17 am
Hi all,
I've currently got a 2 node SQL Server 2008 R2 Enterprise cluster (Cluster_A). This works fine at the moment in a typical configuration, utilising SAN storage. However, we've recently been having SAN access issues, and so I have been tasked with introducing database mirroring in case our SAN fails again and takes our cluster offline.
Now, I want the cluster to operate in a synchronous mode, with a witness. BUT, I want a manual failover control, not automatic failover. The reason for this is that if the cluster is failing over between nodes (planned or unplanned), I do not want the witness to fail over the mirroring session to the mirrored instance (as the databases will be offling briefly during cluster failover). I am only going to make the databases fail over to the mirror if the SAN fails and is going to be offline for a considerable time and I need databases online and serving clients.
Now, in the database mirroring configuration via SSMS (not T-SQL), if I select "High safety without automatic failover (synchronous), the text box detailing the witness server blanks out. I can obviously add this information back in manually, but I want to make sure that this will not affect anything, and that the way I am wanting to configure and run database mirroring will work correctly.
If I choose to manually fail over the mirroring to the mirror instance. My method of rollback will be a manual backup of the database from the mirror, restoring it to the cluster, and then manually failing over the mirroring back to using the cluster as the principal.
Does this all make sense, and will it work?
Thanks!
August 15, 2012 at 2:38 am
liteswitch (8/15/2012)
Now, I want the cluster to operate in a synchronous mode, with a witness
Let's clear the confusion first, you mean mirror session not cluster
liteswitch (8/15/2012)
if the cluster is failing over between nodes (planned or unplanned), I do not want the witness to fail over the mirroring session to the mirrored instance (as the databases will be offling briefly during cluster failover). I am only going to make the databases fail over to the mirror if the SAN fails and is going to be offline for a considerable time and I need databases online and serving clients.
yes this is something to think about, you'd be surprised how many people don't and then can't work out why their mirror session goes bonkers when the cluster goes offline.
One thing you will need to do is a test on your cluster to see exactly how long failover takes, take this calue in seconds and add a bit and then increase the miror session timeout from the default 10 secs 😉
liteswitch (8/15/2012)
Now, in the database mirroring configuration via SSMS (not T-SQL), if I select "High safety without automatic failover (synchronous), the text box detailing the witness server blanks out.
That is by design, if you dont want automatic failover in high safety then you don't have a requirement for a witness
liteswitch (8/15/2012)
If I choose to manually fail over the mirroring to the mirror instance. My method of rollback will be a manual backup of the database from the mirror, restoring it to the cluster, and then manually failing over the mirroring back to using the cluster as the principal.
In high safety synchronous without witness you dont need to take the manual backup. When the first manual failover occurs you will be failing over from clustered principal to the mirror which will then become live. Once the cluster is back up and online and the databases are synchronised you simply perform a manual failover back to the original clustered primary.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 15, 2012 at 3:30 am
Thanks for the clarification. And yes, that last point is critical. Once the cluster comes back online, and available again, I need to understand how long it will likely take to synchronise back with the mirrored database before I can then manually fail over back.. I suppose it depends on the volume of transactions committed to the mirror while the cluster was offline, but I can do tests and simulations for that..
Where in the UI can you set the thresholds for the failover, or is it a T-SQL process only? Mind you, as I'm doing a manual failover, this doesn't reallly matter I guess..
August 15, 2012 at 4:33 am
you will need to throroughly test this, set the timeout via T-SQL
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply