In continuation to the Availability Groups series, here you have another curiosity coming to make your job easier and help you to provide a solution to your customers 🙂
This time we will be talking about listeners. Basically, we will be talking about the limitation of one listener per Availability Group. If you never tested / tried this, this is what happens when you already have a listener in the Availability Group, and try to create another one:
To be honest, I have no idea why Microsoft imposed this limitation. In general, one listener should be enough, but there are cases where more than one listener would be handy.
Listener on Availability Groups
Listeners are not a mandatory in order to setup an Availability Group, but are desirable in order to have a good setup. The purpose of a listener on SQL Server 2012 and 2014 is redirect the connections to the primary replica or secondary replica, depending on the connection string. The listener is a Virtual Network name (VNN), associated to one or more IPs and is reachable using a specified port.
When a failover happens, the listener will redirect to the “new” primary replica.
In the case where a read-only routing is set, all the read-intent connections will be redirected to the proper secondary read-only replica.
This means that the application have no need to know the name / IP of the physical replicas of the Availability Group, in order to setup the connection.
Why I would need another listener?
This is the question that I usually hear. The answer is: there are legacy applications where you cannot change the connection string. When you need to migrate the underline database to another instance, there are two solutions to workaround that problem:
- Create a DNS pointer to redirect the connections to the new instance.
- Create a local alias in the application’s server.
Both of those solutions are out of the DBA’s control and, by the way, the described problem is not something that would make me create mora than one listener in an Availability Group, right?
Ok, what if you have a consolidation project on your hands, and the purpose is migrate some different databases, supporting multiple legacy databases and you also need to migrate the deprecated Database Mirroring solution to Availability Groups? Now this is more complex. So let’s see an example:
Basically, the image is showing the described scenario, where we migrated 6 databases from 3 old SQL Server 2005 instances to a brand new SQL Server 2014 instance. All those six databases are going to be part of the same Availability Group, but the applications are not able to have their connection string changed. We need six listeners created here, but only one is allowed. Now what?
How to create more than one Listener in an Availability Group?
First of all I need to show this:
You can see the original text here.
This means that Microsoft is not recommending a change in the Availability Group by using other tool than SQL Server Management studio.
However, the same Microsoft, posted this:
You can access this article using this link.
I will describe, step-by-step, how to add more than one listener in your Availability Group, by skipping the SQL Server Management studio, which is not recommended by Microsoft, so have more than one listener in your instance may not be supported by Microsoft.
This process was tested in a SQL Server 2012 and a SQL Server 2014 instance, and worked like a charm! I also tested in the SQL Server 2016 CTP 2.1, but unfortunately the SQL Server is not recognizing the new listener.
The receipt
You will need:
- 1 unit of SQL Server Management Studio
- 1 unit of an AlwaysOn Failover Cluster
- 1 unit of an AlwaysOn Availability Group
- 1 or more IPs
- 1 unit of a Virtual Network Name (VNN)
The steps:
- Open the SQL Server Management Studio (SSMS)
- Open the Failover Cluster Manager.
Go to “Run” (or just click
) ➜ Write “cluadmin.msc” ➜ Press “Ok” Go to the “Roles” node and select the role with the same name of the Availability group that you want to add the listener. In my case the AG’s name is “AG02”, so I need to select the “AG02” role.
Right-click on the role, select “Add Resource” and click on “Client Access Point”.
Now you will need to fill the Virtual Network Name and the relate IP, as bellow.
Click “Next” and confirm the inserted values.
If everything looks good, click next and wait to the resource to be created In the role. When the process is done, you will see the following.
- Just click “Finish” to close the window.
Now you will be able to see the resource in the role.
As you can see, both the Virtual Network Name and the IP address are offline. So right click the Virtual Network Name, and click on “Bring Online”. This will force the IP to be taken online as well.
Now that both VNN and the IP are online, we need to take another step to make it visible to SQL Server. Just right-click the AG resource (the one with the same name given to the AG and the same name of the Role) and go to Properties, as bellow:
With the resource properties opened, go to the “Dependencies” tab and add a dependency on the Virtual Network Name (VNN) as you can see in the image. Do not forget to set the condition from “AND” to “OR”, otherwise if one of the listeners fail, the entire Availability Group will be taken offline.
- Click “Ok”, and you are done with the Failover Cluster part.
Now go to the SQL Server Management Studio, expand your Availability Group and see the “Listeners” node.
You will notice that the new listener is now visible from SQL Server!
If you go to the Lister properties, you will notice that the port value is missing, so you will need to fill this and click in the “Ok” button in order to have the new listener working properly.
- You are done!
) ➜ Write “cluadmin.msc” ➜ Press “Ok”
I hope this was useful for you and you keep reading the following articles about Availability Groups curiosities
Thank you for reading!
With experience working in Portugal, Holland, Germany and United Kingdom, he's always available to learn and share his knowledge, in order to contribute to SQL Server community,
View all posts by Murilo Miranda
- Understanding backups on AlwaysOn Availability Groups – Part 2 - December 3, 2015
- Understanding backups on AlwaysOn Availability Groups – Part 1 - November 30, 2015
- AlwaysOn Availability Groups – Curiosities to make your job easier – Part 4 - October 13, 2015