Availability Groups on default and named instances with DR

Question

Availability Groups on default and named instances with DR

chinna1990

Ten Centuries

Points: 1005
More actions
November 29, 2017 at 9:12 am

#324869

Hi Experts,
We have a below requirement to build sql servers with availability groups.
Default Instance:
Server1 (primary) , Server2 (sync) , Server3(async - DR),Server4(async-DR)
Named Instance:
Server1(Primary),Server2(Sync)
Named Instance:
Server3(Primary),Server4(Sync)
Could you please provide your thoughts on below questions:
1) What will be the challenges regarding ports (1433 and 5022) reuse for listeners and end points?
2) Will there be any issues with the cluster, as there will be 4 nodes added to cluster but only two nodes are participating in Always On for named instance scenario ?
3) Will there be any performance overhead for configuring two AG instances in same server ?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

chinna1990 - Wednesday, November 29, 2017 9:12 AM
1) What will be the challenges regarding ports (1433 and 5022) reuse for listeners and end points?

For the default instances, 1433 will be used. For your named instances it will pick a port at random. When you create the listener for each Availability Group you can then pick what port you want to use for the listener.
For 5022, you would need to change it for the named instances to something else, say 5023 (S1/S2) and 5024(S3/S4) to ensure that the traffic is routed to the correct instances.

2) Will there be any issues with the cluster, as there will be 4 nodes added to cluster but only two nodes are participating in Always On for named instance scenario ?

No issues, as the listener will deal with the routing to the right node

3) Will there be any performance overhead for configuring two AG instances in same server ?

"It depends", as your going to have two active instances running on Server1, your going to have to ensure you have given enough CPU and RAM resource to cover both workloads, again this goes for the other nodes as anyone of them could be running a dual workload so you need to ensure you profile, baseline, monitor and increase/decrease as necessary on resources to avoid performance issues.

doug 88182 Old Hand Points: 327 More actions · Answer 2

So I had a weird setup.

Client needed to migrate from a legacy server that had 4 instances, where instance 1 was the default instance on port 1433.
They wanted to use availability groups for the new build, not a shared storage cluster
Combining the 4 instances into a single instance would cause security bleed and naming collisions
Building 4 separate clusters over 8 servers was not in the budget for hardware or licensing

I did all of the AG setup via TSQL SQL templates from my tool kit 'cause I've build a few SQL AG's before.

Here's my build notes:

(1) There were two Server 2016 computers, ServerA & ServerB in a WSFC
(2) Each server has 4 2016 SQL instances with instance 1 being the default instance on 1433 and the named instances got random high ports during the install
(3) The instance 1's were put into AG1, instance 2's in AG2, etc
(4) Each AG was set up with a different hadr end point port and that worked fine, 5022 thru 5025
(5) Each AG got an AGL with a unique IP address, all on port 1433. Can't use the same IP address for two AGLs
(6) SqlServer gave me warnings for AG2, AG3, & AG4 about using the same port, but I figured it would be OK since they were separate IPs
(7) Initial testing suggested that the AGL names AGL1, AGL2, AGL3, AGL4 were all resolving OK, but maybe I didn't look deep enough
(8) Then I went back and reset the SQL ports on all instances to match the legacy server ports from the old 2012 stand alone server
(9) A short time later, I discovered that names AGL1, AGL2, AGL3, & AGL4 were all forwarding to the primary INST1 which was bad
(10) So I went back and recreated the AGL names AGL1, AGL2, AGL3, AGL4 using the the same port numbers as the SQL instances
(11) SqlServer didn't give warning when creating AGL's for AG2, AG3, & AG4 like I saw (6)
(12) The server names AGL1, AGL2, AGL3, AGL4 were all still forwarding to primary INST1
(13) Then I noticed that each AGL IP address respond on all 4 SQL ports & SQL browser port UDP 1434
(14) Server names like AGL2\INST2, AGL3\INST3, AGL4\INST4 go to the correct instances on the correct server
(15) I can do undocumented stuff like AGL1\INST3, which will take me to instance 3 on the server that is primary for instance 1

I guess I just didn't notice that I was going to the wrong instances when I tested in step (7). That's the only explanation. I don't have time to redo the whole thing to validate.
Seems like I could theoretically use a single AGL for all instances, as long as all of the primary instances are always on the same server, but yeah, don't do that.