locked
Multiple Instances and AlwaysOn Availability Groups RRS feed

  • Question

  • 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

    Tuesday, April 23, 2013 9:34 PM

Answers

  • 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

    • Marked as answer by EuroTechie2013 Wednesday, April 24, 2013 4:29 PM
    Tuesday, April 23, 2013 11:02 PM

All replies

  • 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

    • Marked as answer by EuroTechie2013 Wednesday, April 24, 2013 4:29 PM
    Tuesday, April 23, 2013 11:02 PM
  • Hi,

    I got this working last night by using a different endpoint name, endpoint port and listener for each instance. Failover worked fine :-)

    Wednesday, April 24, 2013 4:21 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.

    Monday, November 2, 2015 2:45 PM
  • 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. 

    Friday, May 20, 2016 4:44 AM
  • 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

    • Proposed as answer by kammara Wednesday, October 17, 2018 8:00 AM
    Monday, July 25, 2016 10:32 PM
  • 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

    Wednesday, July 27, 2016 2:12 AM
  • 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


    Thursday, August 18, 2016 9:13 PM


  • 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:00 AM
  • Please give the  tempary IP & instance  for Node wise Node A & Node-B and Node-C


    Wednesday, October 17, 2018 8:03 AM