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?
StorageMicrosoft SQL Server 2008Disaster Recovery

Avatar of undefined
Last Comment
arnold

8/22/2022 - Mon
itprotocall

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 
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.
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...
Your help has saved me hundreds of hours of internet surfing.
fblack61
arnold

Mirroring or log shipping is an option.
Network_Padawan

ASKER
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?
arnold

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Network_Padawan

ASKER
So what exactly does SAN to SAN replicate? The file level or block level of the contents of the hard disks?
arnold

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?
Network_Padawan

ASKER
>>>>>The purpose of the DR is a hot standby in the worst case scenario?

yes thats right.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
arnold

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.
Network_Padawan

ASKER
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?
arnold

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.


Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Network_Padawan

ASKER
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
arnold

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Network_Padawan

ASKER
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?
arnold

I think, Mirroring works as long as it can reach the other location without regard to the segments/subnets.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck