March 10, 2015 at 2:07 pm
Hello,
I'm planning out how to best deploy an AlwaysOn availability group model in an environment with two separate datacenters (in the same domain) with 2 SQL Server instances in each datacenter that will be part of a WSFC, so 4 total nodes in the cluster. We want to have 2 AlwaysOn AGs in this cluster, one to host the databases for software in datacenter 1 and the other to host databases for software in datacenter 2. This is the current model I'm looking at:
[p]
Datacenter 1
* SQL instance 1
- primary replica of AG1
* SQL instance 2
- secondary replica of AG1 with synchronous replication
- secondary replica of AG2 with asynchronous replication
Datacenter 2
* SQL instance 3
- primary replica of AG2
* SQL instance 4
- secondary replica of AG2 with synchronous replication
- secondary replica of AG1 with asynchronous replication
[/p]
From a technical perspective it looks like this will work, but I'm trying to make sure this is actually the best way of doing things. Some of the questions I'm trying to figure out are:
- If there is a failure of a node in the cluster, how do we make sure that the primary AG1 replica stays in datacenter 1 and the primary AG2 replica stays in datacenter 2 if possible
- If we have a listener configured for each AG, are there any gotchas as far as setting up connection strings to be aware of (like making sure that port info is included in the connection)
- Are there better SQL Server features to use for HA across datacenters like this? We want to try to do as little work possible in the event we need to fail over between nodes or datacenters.
- Are there problems with having secondary replicas of two different AGs on the same node based on thread usage for redo and the number of dbs per AG?
March 11, 2015 at 4:12 am
friedrichsenm (3/10/2015)
Hello,I'm planning out how to best deploy an AlwaysOn availability group model in an environment with two separate datacenters (in the same domain) with 2 SQL Server instances in each datacenter that will be part of a WSFC, so 4 total nodes in the cluster. We want to have 2 AlwaysOn AGs in this cluster, one to host the databases for software in datacenter 1 and the other to host databases for software in datacenter 2. This is the current model I'm looking at:
[p]
Datacenter 1
* SQL instance 1
- primary replica of AG1
* SQL instance 2
- secondary replica of AG1 with synchronous replication
- secondary replica of AG2 with asynchronous replication
Datacenter 2
* SQL instance 3
- primary replica of AG2
* SQL instance 4
- secondary replica of AG2 with synchronous replication
- secondary replica of AG1 with asynchronous replication
[/p]
From a technical perspective it looks like this will work, but I'm trying to make sure this is actually the best way of doing things.
Seems like a little thought has gone into this, that's very important.
friedrichsenm (3/10/2015)
Some of the questions I'm trying to figure out are:- If there is a failure of a node in the cluster, how do we make sure that the primary AG1 replica stays in datacenter 1 and the primary AG2 replica stays in datacenter 2 if possible
As both sites are operational and no one site is truly a DR site, you'll need to configure a witness in the form of a fileshare witness, this must be located on a separate third site.
friedrichsenm (3/10/2015)
- If we have a listener configured for each AG, are there any gotchas as far as setting up connection strings to be aware of (like making sure that port info is included in the connection)
You only need to include the port number if you don't use TCP 1433. Multi subnet IPs can be an issue, there are articles around this on MSDN and TechNet, a simple search will provide this info
friedrichsenm (3/10/2015)
- Are there better SQL Server features to use for HA across datacenters like this? We want to try to do as little work possible in the event we need to fail over between nodes or datacenters.
Failover cluster instances are another way but they provide instance level HA and not database or database group level.
friedrichsenm (3/10/2015)
- Are there problems with having secondary replicas of two different AGs on the same node based on thread usage for redo and the number of dbs per AG?
Not really specific to databases from separate groups, more just the fact of the number of readable secondarys on a replica. GFor more info on readable secondarys check MSDN\Technet
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 11, 2015 at 8:14 am
Thank you for replying!
Your notes about the fileshare witness and AG listeners in a multi-subnet scenario were very helpful. I was able to find some of the documentation I needed for them.
Here's the main site I used to get info about AG listeners in a multi-subnet environment in case there are future readers of this post
March 11, 2015 at 11:43 am
please don't forget to mark answers as correct as this may help others
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply