none
Can I use the same Listener for more than one Availabilty Group? RRS feed

  • Question

  • Hello All,

    I've been doing some research on availability groups in SQL, however have come up with some questions that I cannot find answers to in the documentation.

    >>>>>CONFIGURATION SETUP<<<<<<

    I have a 4 node sql server setup with WSFC.  No shared storage, using Cloud witness as quorum.  Two nodes exist in Azure (subnet A) and two nodes exist on prem (subnet b).  Subnets communicate via VPN between azure and on prem and all nodes can communicate with each other.  Cluster is up and healthy. (AZSQLCLUST NetBIOS name 10.20.0.254)

    In azure I have AdventureWorks configured on NODE1 in azure.  I setup an Availability Group (ADWKAG1) to create a secondary synchronous replica of AdventureWorks on the NODE2 in Azure.  The future intent here is to setup an Asynch copy of this database to the on prem nodes (they are there for forced failback only).

    I've setup a listener (ADWKAG1-ls), it is configured to use the same IP as the cluster name 10.20.0.254.  

    >>>>>QUESTION<<<<<<<

    If I build another availability group setting up a new Database (AdventureWorks2) on NODE 2 in Azure, with a Synchronous replica to NODE 1 in azure.  Do I need another Availability Group listener or can I use the listener that I've created already?

    I know each AG needs a listener, but it doesn't say whether that listener needs to be unique or not.

    Thanks for the help

    r/
    john

    Friday, January 12, 2018 3:52 PM

Answers

  • The listener has to be unique. When you attempt to create the listener you will get an error message that is difficult to understand, but basically the listener setup program detects that it is in use.

    Multiple AGs on the same nodes can share a listener.

    • Marked as answer by johnwildes Friday, January 12, 2018 6:50 PM
    Friday, January 12, 2018 4:10 PM
  • That is correct, but what may happen is that you may failover AG1 to Node2. The listener now thinks that Node2 is the writeable node. It thinks that Node 1 is the read only node. AG3 is still on Node1. Applications connecting to the listener using application intent readonly will now connect to Node1 and applications connecting to the listener with read write will be connecting to Node2 for all AGs. This may or may not be what you want.
    • Marked as answer by johnwildes Friday, January 12, 2018 6:50 PM
    Friday, January 12, 2018 4:46 PM

All replies

  • The listener has to be unique. When you attempt to create the listener you will get an error message that is difficult to understand, but basically the listener setup program detects that it is in use.

    Multiple AGs on the same nodes can share a listener.

    • Marked as answer by johnwildes Friday, January 12, 2018 6:50 PM
    Friday, January 12, 2018 4:10 PM
  • Hilary,

    Thanks...so just to confirm you stated that Multiple AGs on SAME NODES can share a listener.

    When I get to production you're saying if I setup an environment like the one below I should be ok.

    CLUSTER (10.20.0.254)

    NODE 1
    AG1(primary)  AG2(secondary)
    AG3(primary)  AG4(secondary)
    AG5(primary)  AG6(secondary)

    Listener

    NODE1-ls (10.20.0.253 for all ag on NODE 1)

    NODE 2
    AG2(Primary)  AG1(secondary)
    AG4(primary)  AG3(secondary)
    AG6(primary)  AG5(secondary)

    Listener

    NODE2-ls (10.20.0.252 for all ag on NODE 2)

    I won't need a new unique listener for every AG?

    Thanks again for the help. 
    r/
    john

    Friday, January 12, 2018 4:21 PM
  • That is correct, but what may happen is that you may failover AG1 to Node2. The listener now thinks that Node2 is the writeable node. It thinks that Node 1 is the read only node. AG3 is still on Node1. Applications connecting to the listener using application intent readonly will now connect to Node1 and applications connecting to the listener with read write will be connecting to Node2 for all AGs. This may or may not be what you want.
    • Marked as answer by johnwildes Friday, January 12, 2018 6:50 PM
    Friday, January 12, 2018 4:46 PM
  • Ok, so in this instance then I want a listener for every AG at this point so that if I fail over one of the AG to another node the remaining AG will work as appropriate.

    So in the architecture above I need 6 listeners all Unique IP.

    r/
    john

    Friday, January 12, 2018 6:25 PM
  • From what you describe, yes. 

    Friday, January 12, 2018 6:52 PM
  • Hilary, should your statement in this paragraph be "Multiple AGs on the same nodes cannot share a listener"?

    I say that based on "The listener has to be unique" and "you will get an error message" and "the listener setup program detects that it is in use".

    Monday, October 22, 2018 8:13 PM
  • This link describes how to do this.

    https://blogs.msdn.microsoft.com/sqlalwayson/2012/02/03/how-to-create-multiple-listeners-for-same-availability-group-goden-yao/

    It is not something you can do in the wizard.

    The OP says this:

    I've setup a listener (ADWKAG1-ls), it is configured to use the same IP as the cluster name 10.20.0.254.  

    When you go to create the listener you will get an error message because the IP address is already in use.

    The listener will redirect to the primary for the AG. You will be able to see all the databases on the primary whether they are protected by the AG or not.

    Monday, October 22, 2018 8:30 PM