Link to home
Start Free TrialLog in
Avatar of Network_Padawan
Network_Padawan

asked on

Sql Clustering with mirroring options

Hi all,

I posted a few days ago a question pertaining to a solution I am trying to design for our sql database servers, and I have a question regarding the answers I got which I just thought of.

I wanted to setup a redundant SQL cluster in site1 and somehow replicate the data to Site2 for DR purposes. I was advised the best solution is to have a SAN and form a cluster for local redundancy in site1 and do an asynchronous replication to siteb. What I dont understand is asynchronous replication is obviously mirroring....but how do I set that up using the cluster if its design to failover to a passive node if the active fails? Mirroring uses async replication with a witness for failover...but I don't understand how this would work if during a failover I obviously want the passive node in the cluster to take over the requests instead of a mirror-induced failover to the DR site...

Is there a way to setup async replication without mirroring?
Avatar of itprotocall
itprotocall
Flag of India image

i think you may just need a commercial solution to implement asynchronous replication with mirroring, check out teh comments on this other related question:
https://www.experts-exchange.com/questions/25082114/SQL-replication-ways.html 
Avatar of arnold
It is not clear what you mean.

In a way it is mirroring data the question is really on what level.
You can have a SAN to SAN mirroring geographic cluster

nodea          nodeb
sana            sanb
sitea            siteb

You can configure database mirroring on the clustered group to a standalone or another cluster group in a different location.

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/91597c4e-3337-48d9-9fa8-c1b8a659cb41
http://msmvps.com/blogs/jtoner/
http://sqlcrunch.com/Clustering/tabid/88/Default.aspx

You could also use log shipping to transfer the transactions from site1 to site2.

Mirroring is often synchronous especially with a witness.

If you want the cluster to handle failover between failed nodes, do not include a witness in the mirroring setup.  In the event of a DR event, you would need to assert the site2 DB to bring it online at which point, when the clustered location comes back, you would have some steps you would need to deal with to reestablish mirroring with site2 as principal and then once the clustered instance synchronizes fail the mirroring back.
Avatar of Network_Padawan
Network_Padawan

ASKER

Hi guys, Ok just to simplify what I am asking is, I want to create a sql cluster at Site1, and replicate either the active or passive sql database to site B. I need to use async replication...
Mirroring or log shipping is an option.
Thanks arnold., one final question before I close this down and assign points....

If i have a SAN with 3 nodes in an active/passive cluster, and I want to replicate that sql datababse to SITE B, is it better to do async replication WITHOUT mirroring or should is SAN to SAN replication better? In other words, buy a second SAN for site b and replicate that way?
I am uncertain whether the SAN to SAN replication will work with SQL.
http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/5b261deb-521c-44ca-9495-814002623049
http://www.las-solanas.com/storage_virtualization/asynchronous_replication.php

The issue in the approach you take differs in the means/process you would have to follow in the event of site A becomes inaccessible and then the recovery process when it comes back.
So what exactly does SAN to SAN replicate? The file level or block level of the contents of the hard disks?
I think it is Block level. But it might depend on the SAN.
I'm not totally sure.
I think an SQL level mirror/logshipping might be.
The purpose of the DR is a hot standby in the worst case scenario?
>>>>>The purpose of the DR is a hot standby in the worst case scenario?

yes thats right.
You have to determine which plan is optimal in the event of a false DR implementation. i.e. the Primary location lost power/total connectivity loss.
The consideration is if you use one approach, the process to failback can become extremely complex.

As you've no doubt found, this is an extremely complex issue and many moving parts.
mirroring/logshipping might be simpler to recover from.
I.e. on failure, you alter the setup on the DR site bringing the DB online whether it was in recovery and being updated by logshipping or through mirroring.  When the primary site comes back, you would need to restore the DB from the DR site, reestablish mirroring and then perform the mirroring failover.
If you have the luxury of being offline, you could backup the DR DB, restore it on the primary site and then go through the process of reestablishing logshipping/mirroring from the primary to the DR once everything is working again on the primary side.
hey arnold, your right this entire project is giving me a large headache. I dont want to use mirroring with synchronous replication across a WAN connection due to latency issues....can I just implement async replication without mirroring and if the primary site goes down, activate the the secondary, and when the primary is back up, set up a mirror standby on the primary and then fail it across?
The problem with either mirroring (without a witness) and log shipping is that once you bing up the database at the DR location the transition back is complex.
I.e. in a mirror with witness at a yet third location, such that the mirror auto failsover to the DR when the primary is down, when the primary comes backup it in checking with the witness will auto configure to be the mirror and will auto synchronize with the principal at the DR. The failback is as simple as going through the mirroring settings and hitting the failover button when the mirror monitor reflects that they are in sync.
The difficulty is that in a cluster environment the transition from the active node to the passive node could trigger the auto-failover to the DR which is not a desired behavior.

The other option is to use SAN copy with the DR location being a node on the cluster which you mentioned, but the inclusion of the mirror consideration threw me onto a path to address the setup with mirroring in mind.

I.e. GEO cluster
http://synsol.eu/blog/2009/11/error-installing-sql-2008-geo-cluster-rule-cluster-shared-disk-available-check-failed/
http://sqldbpool.wordpress.com/2009/10/07/sql-server-2008-active-passive-cluster-installation/

You might have an option to check with the SAN vendor on their recommendations/insight into this type of setup.  The difficulty might be with whether you have network level IP shift capabilities. or the applications that rely on the database have the logic in the event the primary connection is not available to attempt a fail-over one.


This is all so complex. I cant seem to settle on a solution. We absolutely need clustering at our primary site, that theres redundancy there and thats locked in, its just the DR component thats difficult.

By what you are telling me its a catch 22. Either we opt for the mirror with a witness solution in which case any sort of down time for whatever reason could trigger the mirrored db to be active instead of the passive node in the cluster, however the swap back to the primary site is simple as its as safe as pressing a "failover" button.

On the other hand, we could do async replication without mirroring but if a disaster did occur and our primary site went down (that is all our clustered nodes were down), when we bring it up but then to revert back, we would find that we would have to setup mirroring with a witness to have the db in sync, and then trigger a failover to the primary, undo the mirror.

Is this basically it?
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hmm I see the issue here. Ok just last question and then Ill close this thread. If I setup a configuration whereby the db does async without mirroring, and that mirror site is in another colocation with a different ip scheme, but are routable so they can ping each other and reach each other...does sql mirroring support different subnets for replication?
I think, Mirroring works as long as it can reach the other location without regard to the segments/subnets.