Multiple Instances and AlwaysOn Availability Groups
Question
Tuesday, April 23, 2013 9:34 PM
Hi,
I have 2 SQL Servers: A & B
Each has 2 instances and each instance is part of an database AlwaysOn Availability Group
Server A - instance 1, AlwaysOn Group1 & instance2, AlwaysOn Grroup2
Server B- instance 1, AlwaysOn Group 1 & instance2, AlwaysOn Grroup2
I configured 2 AlwaysOn groups, which worked for a brief amount of time:
Server A - had instance 1 (primary) and instance 2 (secondary)
Server B - had instance 1 (secondary) and instance 2 (primary)
However after a few minutes, Server A instance 1 (secondary) changed to the not synchronized state and I'm struggling to add Server A instance 1 as a replica in AlwaysOnGroup1
Can someone tell me if I'm doing something wrong? Can I have 2 instances participating in separate AlwaysOn Database Availability Groups on the same server?
I'm wondering maybe the configuration is failing because one server holds a primary and the other a secondary - perhaps each server should hold only a primary or secondary?
Thanks
All replies (9)
Tuesday, April 23, 2013 11:02 PM âś…Answered
Hi there,
Need to change the design when you have multiple instances if you want to manage it in simple way.
make one instance and have two groups. hwy do you need two instancess in the first place.
If you wanted to do it in your way you need to take good care of post settings & end points in the availability groups.
Good luck
kumar
Wednesday, April 24, 2013 4:21 PM
Hi,
I got this working last night by using a different endpoint name, endpoint port and listener for each instance. Failover worked fine :-)
Monday, November 2, 2015 2:45 PM
Hi Kumar, I'm afraid you are running in an unsupported deployment. To quote from Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server):
"Availability replicas must be hosted by different nodes of one WSFC cluster: For a given availability group, availability replicas must be hosted by server instances running on different nodes of the same WSFC cluster. The only exception is that while being migrated to another WSFC cluster, an availability group can temporarily straddle two clusters."
This is true also with SQL FCIs co-existing on the same cluster node (assuming failover) -so you need to watch for that configuration too.
You should change your design to a single SQL Standalone Instance per cluster node and use multiple Availability Groups.
Regards,
Mark Broadbent.
Microsoft Certified Master
Contact me through twitter | blog | sqlcloud
Please click "Propose as answer" if a post solves your problem
or/and vote the post up if it's been helpful.
Friday, May 20, 2016 4:44 AM
Just searching for an answer to something else came to this post. So, "retracement" is this still the case? is it unsupported to have multiple instances on the same cluster node and having availability groups on them. Our current set-up is
Node1 Instance1(AG1) is active Node2 Instance1(AG1) is passive
Node1 Instance2(AG2) is active Node2 Instance2(AG2) is passive
its working fine and we do not have any issues at the moment,
but we cannot have Node1 instance1 with two(AG1 AG2), because some instance level properties like collation etc does not support to the other application databases.
Monday, July 25, 2016 10:32 PM
EuroTechie2013 and aminifdo,
We are working with the same configurations you've got working. How are you handling access to each instance on each node? Each named instance on each node gets an IP and a shared listener. The listener will automatically register in DNS, but you don't get that with the named instance's IPs. So for example, what if you want to connect to the primary node for instance 2? Are you creating hostname DNS entries for your IPs in use for each instance's nodes? You would have for a multi-subnet FC:
System Node A IP 10.1.1.5 (DNS sql-node-a.contoso.com)
Node A's instance1 IP 10.1.1.10
Instance1 AG1 Listener IP 10.1.1.11 (DNS sql-ag1-li.contoso.com)
Node A's instance2 IP 10.1.1.20
Instance2 AG2 Listener IP 10.1.1.21 (DNS sql-ag2-li.contoso.com)
System Node B IP 10.1.2.5 (DNS mssql-node-b.contoso.com)
Node B's instance1 IP 10.1.2.10
Instance1 AG1 Listener IP 10.1.2.11 (DNS sql-ag1-li.contoso.com)
Node B's instance2 IP 10.1.2.20
Instance2 AG2 Listener IP 10.1.2.21 (DNS sql-ag2-li.contoso.com)
Attempting to connect to instance2 on Node A:
Doesn't work: mssql-node-a.contoso.com\instance2
Does work: 10.1.1.10\instance2
We were planning to create DNS host entries for the named instances on each node.
I.e., sql-nodeA-in1.contoso.com, sql-nodeB-in1.contoso.com
Thanks for any additional pointers.
-Joe
Wednesday, July 27, 2016 2:12 AM
This is very informative, thank you all. Joe, shouldn't you try to connect to instance2 on Node A using the Node A's DNS name? As in sql-node-a.contoso.com\instance2? I think I may be missing something here.
Leroy G. Brown
Thursday, August 18, 2016 9:13 PM
Did some more research on this and seems like it's not an "unsupported" setup as retracement mentioned. The quote he posted from "Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups" basically means that you can't have 2 replicas for a GIVEN (keyword) availability group on the same cluster node, for example:
Node 1 - Instance A - AG1 (primary)
Node 1 - Instance B - AG1 (secondary)
So you definitely can have 2 instances on the same node, hosting availability groups, as long as they're not hosting replicas of the same availability group.
Here's a post from dba.stackexchange.com discussing the topic:
http://dba.stackexchange.com/questions/87140/multiple-named-instances-with-always-on
Leroy G. Brown
Wednesday, October 17, 2018 8:00 AM
Node1 Instance1(AG1) is active Node2 Instance1(AG1) is passive and Node3 Instance1(AG1) is passive
Node1 Instance2(AG2) is active Node2 Instance2(AG2) is passive and Node3 Instance2(AG2) is passive
Is this workable solution , if yes please share me the below details tentative ip details
Wednesday, October 17, 2018 8:03 AM
Please give the tempary IP & instance for Node wise Node A & Node-B and Node-C
P