Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Mirroring with clustering Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 2:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:35 AM
Points: 69, Visits: 565
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!
Post #1345159
Posted Wednesday, August 15, 2012 2:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 6,619, Visits: 14,185
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"
Post #1345167
Posted Wednesday, August 15, 2012 3:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:35 AM
Points: 69, Visits: 565
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..
Post #1345182
Posted Wednesday, August 15, 2012 4:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 6,619, Visits: 14,185
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"
Post #1345198
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse