How to create multiple listeners for same availability group (Goden Yao)

Blog : How to create multiple listeners for the same availability group

As the PM who designed the availability group listener feature, when I talked to some customers , there’re 2 scenarios I realized may need to have multiple listeners for a single availability group.

Scenario 1:

This Customer has a few legacy clients and he cannot change the connection string for them. Plus, these legacy clients used to use 3 different instance names to connect to database. Now since they want to leverage AlwaysOn Availability Group Listener, without changing all legacy client connection string, they can only 1 kind of 3 client work if they switch to AlwaysOn.

We thought though 2 possible solutions: CNAME vs. Multiple listeners – the latter one is kind of my favorite secret J since from SQL you can only create 1 listener per availability group.

We compared 2 solutions below and they finally go with multiple listeners

 

	Pros	Cons
Multiple Availability Group listeners	DBA can configure each listener separately (e.g. assign different IPs, firewall rules, enable/disable some of them, etc.) More visible to DBA from management perspective. DBA can check sys.dm_tcp_listener_states (newly added in SQL Server 2012) to check real-time client connection on all these 3 listeners	Increase the risk: any one of 3 listeners is down, some of the legacy clients won’t work. (we assume CNAME should have less risks) Probably longer failover time (a couple more seconds): Windows Failover Cluster need to failover all 3 listeners instead of 1
DNS (CNAME)	Static , less possibility to have issues or unexpected error.	Need separate permission – DBA usually doesn’t have permission to manipulate DNS records directly.

Scenario 2:

There’s another customer told me he uses NUMA system with SQL. The interesting thing he does with SQL is to make SQL listen to multiple ports and system allocates different resources to the client based on the port it connects to. (That’s barely what I learned from him)

So he also wants to configure a few more listeners when availability group comes to the picture so his original logic can still work.

 

How to configure

1>    Setup AlwaysOn Availability Group (agsc4), skip listener creation in the wizard or T-SQL

2>    Go to cluster manager , create “client access point”, you can create multiple of them. (There’s a quota limitation of how many computer objects you may create in one cluster – I think it’s 20 by default)

3>    It looks like the picture below, I created 3 listeners and assigned IPs for each of them.

We recommend you use “OR” dependencies for IPs in each listener but you can use “AND” if required. (e.g. you want a IPv4 and a IPv6 both online to make the listener online)

4>    Make sure all listeners you want to create are online and associate them with your availability group resource – make sure you use “OR” in dependencies – this means as long as one listener is online your availability group is considered online and available to your clients.

 

5>    Now if you query:sys.availability_group_listeners– you should be able to see all of them.

Note: you can see all the ports are “NULL”.

 

6>  Now, you still cannot use these listeners. You need to go back to run T-SQL to assign port for each of them.

I use 1433 (the default SQL listening port) for all of 3, you can definitely assign different port to them (as Scenario 2 described)

alter availability group [agsc4]

modify listener 'agsc4_listener1'

(port = 1433)

alter availability group [agsc4]
modify listener 'agsc4_listener2'
(port = 1433)

alter availability group [agsc4]
modify listener 'agsc4_listener3'
(port = 1433)

7>    Query sys.availability_group_listeners again, you should be able to see they all have ports and SQL is listening to them.

8>    Now connecting to any of the listeners you created, it should bring you to the same instance which host the availability group primary replica:

 

Difference between listener created through SQL and created in Cluster Manager

if you recall, in SQL Server 2012 CTP0/CTP1, we still don’t have integrated manageability or T-SQL experience for our customers to create AG Listener inside SQL. After CTP1, we provided this integrated user flow in SQL. But you can still create listener through Cluster Manager.

So what’s the difference and in which scenario you want to do this?

Here’s a comparison between listeners created through SQL and created in cluster manager:

	Listener Created by SQL	Listener Created in Windows Failover Cluster Manager
Can use immediately?	Yes	No. You must run T-SQL to assign the port to the listener before you can use it.
IP relation inside listener	Only “OR” for all IPs inside listener	You can customize “OR” or “AND” for IPs inside listener to meet your custom need. (e.g. you want to have one IPv4 and one IPv6 both online to make the listener online)
RegisterAllProvidersIP = 1	Yes SQL set this private property to 1 (or true) to make sure DNS registers all IPs inside the listener.   This is to ensure should a failover happen, your client doesn’t need to resolve the new IPs from another subnet (if it’s in multisubnet scenario).	No If you want to get the benefits as stated in the left column – you need to run powershell script to enable this by yourself.
Can be managed through SQL	Yes You can drop , add IP and change port of the listener	Depends You need to check “is_conformant ” value of the listeners you created outside of SQL in sys.availability_group_listeners This property will tell you if you can manage it through SQL or not.   If it’s not conformant, the only thing you can do through T-SQL is to change the port. You cannot drop it or add IPs – these need to be done through Failover Cluster Manager.

 

Last but not least, please use sys.dm_tcp_listener_states to check real time listener connection and status – this dmv is very helpful to trouble shoot your connectivity related issues and we newly introduced it in SQL Server 2012.

Goden Yao, Program Manager

SQL Server Engine High Availability

Comments

• Anonymous
  November 21, 2013
  Good article...  seems like this is a bit of a 'work around' to be able to configure multiple listeners which as mentioned above "since from SQL you can only create 1 listener per availability group".  Is this true so this is not common knowledge and not documented in AG config/install guide? Thanks in advance.

• Anonymous
  December 17, 2013
  Good Article ... Thanks. One other scenario that i am facing, we are setting up SharePoint stretch farm across multiple data centres. These data centres are having different subnet hence one listener in one subnet cannot talk to other secondary replicas which is in a different subnet. One way is to create to multiple listeners ... i havent tested this but hopefully this will work. Thanks

• Anonymous
  December 18, 2013
  Awesome article with all required details to satisfy the own queries. Thanks a bunch for this sharing...

• Anonymous
  February 21, 2014
  this is documented in SQL Server Online help. :)

• Anonymous
  August 26, 2014
  Great stuff, Thank you.

• Anonymous
  March 24, 2015
  Can you configure read-only routing with those listeners? For example if you have 3 SQL servers, 1, 2, and 3. I want one listener for read-only to go to 2,3,1 while second listener would go to 3,2,1.

• Anonymous
  March 24, 2015
  Also, would same work with Windows Server 2012 R2?

• Anonymous
  April 13, 2015
  Followed the process but it fails. Query of the listeners always comes up empty.

• Anonymous
  May 18, 2015
  good article how do you get this to work across multiple subnets ?

• Anonymous
  June 25, 2015
  If not creating multiple ones, will the AG listener become a single failure point of system? or is it just like the WSFC itself? i.e. a resource built with and for redundancy? Thanks!

• Anonymous
  June 25, 2015
  @Simon, if not creating multiple listeners, single listener is still HA-protected as other resources on top of WSFC. The multiple listeners for single AG is specific for the scenarios that I described in the blog.

• Anonymous
  September 01, 2015
  The comment has been removed