SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Mirroring with clustering


Database Mirroring with clustering

Author
Message
liteswitch
liteswitch
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 Visits: 598
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!
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19782 Visits: 17242
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" ;-)
liteswitch
liteswitch
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 Visits: 598
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..
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19782 Visits: 17242
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search