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.
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
Multiple Availability Group listeners
DBA can configure each listener separately (e.g. assign different IPs, firewall rules, enable/disable some of them, etc.)
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
Static , less possibility to have issues or unexpected error.
Need separate permission – DBA usually doesn’t have permission to manipulate DNS records directly.
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.
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?
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)
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
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.