March 11, 2016 at 9:46 am
Hi!
I have a question regarding setting up an Availability Group with the current scenario.
I have two locations under one domain. I have a two node Windows cluster (active/active) mirroring to the other location's two node Windows cluster. Since database mirroring is being deprecated, I would like to create an Availability Group. This setup was nice because if there was a hardware issue, Windows cluster would fail it over to the other server in the same location and keeping users in that location happy since it's housed in the same building. If the primary site was facing an Internet outage, harsh weather, etc, then database mirroring would fail it over manually. Also, the primary site's Internet can face unexpected brief outages randomly.
Since the nodes must all be under one Windows cluster, how can I exactly set it up sort of like my current setup?
March 11, 2016 at 11:50 am
Looks like you have run into one of the limitations of AGs:
"The host computer must be a Windows Server Failover Clustering (WSFC) node. The instances of SQL Server that host availability replicas for a given availability group must reside on separate nodes of a single WSFC cluster. The only exception is that while being migrated to another WSFC cluster, an availability group can temporarily straddle two clusters."
Taken from:
https://msdn.microsoft.com/en-us/library/ff878487(v=sql.120).aspx
March 11, 2016 at 1:16 pm
Might be worth a look:
SQL Server Multi-Subnet Clustering
...and then setup a couple high availability replicas.
March 15, 2016 at 1:34 pm
Can someone please clarify how to set up the SANs with multi subnet clusters?
I have two VMs in one location and two VMs in another location and a SAN in each of the locations. I'm not sure if it's possible to have WSFC failover between the two nodes in one location if there are hardware issues on one of the nodes and have WSFC failover to the other location if that site is down...
March 22, 2016 at 11:59 am
So..............
If I join four nodes to one WSFC, do I create two SQL Server failover cluster install and two Add node to a SQL Server failover cluster? One SQL Server failover cluster per geographic location?
I'm so confused on how to do this... I've done a lot of installing and uninstalling because it's not working...
March 22, 2016 at 12:17 pm
Did you work through this?
Microsoft Windows Multi-Site Failover Cluster Best Practices[/url]
I've never set this up BTW, I just know it's possible.
If you're doing Availability Groups, then you're going to have multiple copies of the data, like mirroring.
If you're doing a standard failover cluster, I believe SAN-SAN replication is a requirement and you'll have one copy of data for each site.
March 23, 2016 at 7:12 am
I'm attempting to do it, but without SAN-to-SAN replication since mine can't do synchronous replication between the two geographic locations.
I'm hoping to do one WSFC with two SQL Server failover clusters (one for each geographic location). I keep running into problems... Yay!
March 24, 2016 at 5:31 am
What you want is a single multi subnet windows cluster that includes all four nodes. You could destroy the cluster at the remote site then add the those nodes into your existing cluster. Remember you dont have any shared storage with always on so no SAN replication is needed. You will install SQL as stand alone instances not as a cluster. Each instance will need its own storage to house the databases. You can install those instances as separate instances on top of you existing clustered instances than move the databases over.
As far as keeping the traffic in one site, that is no problem. You will set the two nodes in the local site to be synchronous replicas with automatic failover. The two nodes at the remote site will be set as asynchronous replicas with only manual failover.
This works very well we use this method with a 4 node cluster with 3 nodes on the east coast and 1 node on the west coast for D.R.
March 24, 2016 at 6:31 am
Thank you!
I was able to create a single Windows Cluster that includes all four nodes and one SQL Server Failover installation per geographic site. That way, it's most similar to the current configuration I have now. I haven't fully tested this configuration yet so I'm not sure if it's even going to work out.
I'm not sure how licensing will be with your setup.
I'm not sure if I mentioned this earlier in the thread, but just so you know how I have it now is:
-Two geographic locations
-Two nodes per geographic location
-One Windows/SQL Server Failover Cluster per geographic location
-Asynchronous Database mirroring between the two locations
-Primary location is active/active with two SQL Server instances
Edit: How come you don't want shared storage with AlwaysOn?
March 25, 2016 at 9:01 am
That will setup will work but can be tricky. in your case it may be fine if you are only doing manual failovers to the remote site. This way the Cluster will be the first H/A line of defense with local cluster failover. Then if you lose the entire site you could manually failover the Avail. Group to the remote site. You have to be careful of your config settings for the AG. When the cluster fails over locally you dont want the Avail. Group to think that the other site if offline. I have never set it up this way but just make sure you check everything carefully it can get really tricky with clusters and avail. groups.
As far as shared storage you cannot used shared storage in an Avail. Group since all replicas in the avail. group have a full copy of the database and that copy is readable. In your setup you can use the shared storage on the cluster since that is seen as a "single replica" in the Avail. Group. You will want the same storage and dir on the remote site for the async copy of the db. Again in your case the cluster is a single instance in the avail. group so it should be fine. Just remember each instance in the Avail. Group must have the same storage and hold a full copy of the database.
March 25, 2016 at 10:42 am
Yes, it will be an asynchronous Availability Group with manual failover. I have done some testing on it and it seems to be working properly. There are a lot of config changes to make...
Ah, yes. That completely makes sense about shared storage. It's just like Database Mirroring.
Thank you very much!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply