We have already configured our Availability Group, now we need to make it flexible and accessible. It’s time to check on how to create a listener in order to make a single access point for you AG!
In continuation to our previous article, we are going to pass to another phase of this setup, as we already have our database in sync and safe, or highly available, depending of the chosen mode/architecture.
Notice that the Availability Groups is so flexible, that we can have multiple groups, in the same base cluster, with databases active on different instances. This way, aside of have a very good solution of high availability and disaster recovery, we will have a great option to balance the utilization of our hardware.
In the previous picture, you can see an example of a configuration involving 3 standalone instances, with 3 Availability Groups setup. If we balance the databases in a proper way, on each Availability Group (AG01, AG02 and AG03) we will be able to keep each Availability Group active in a dedicate node, having the others with a secondary role, which can be read-only, or just closed to connections and acting as a standby database. This situation is comparable to a cluster of two nodes with two clustered instances installed and active on opposite nodes, what people call “active-active” cluster (wrongly by the way).
With the scenario just described, the use of a listener is highly recommended, allowing us to failover the Availability Group without having to worry about change connection strings.
The Listener
So, now it’s time to configure the listener! But first, what is a listener and for what is this used for?
As we have the virtual AD Computer, in a SQL Server clustered instance, we will have the listener in the Availability Group in order to offer a single point of access to a group of databases. To create a listener, we will also need an IP and Computer in the AD, and don’t forget that the cluster computer should have full access to the SQL Server computer account.
In our case we will be using the following cluster, as shown in the part one:
So, the AD Computer name “W2012R2CLT01” should have full access tour listener “SQLSRVAG01”, associated to the VIP 190.100.100.51. Don’t forget to disable the Computer “SQLSRVAG01” after create, otherwise the listener creation is going to fail.
You may be asking: Is the listener required? The answer is “no”! You can survive without the listener, like we always survived when using the database mirroring. Its role is just redirect the connections to the right route. By default it will redirect us to the Primary Replica, where the database is both readable and writable.
In a scenario of a Disaster Recovery environment, where you have a production instance in a datacenter and the disaster recovery instance in another one, is common to see this approach, as the secondary databases aren’t even readable!
Setting up the Listener
So let’s now check the steps to add a listener to an Availability Group!
Assuming that you have the VIP, the AD Computer and that all prerequisites are met, let’s start!
Connect, using the SQL Server Management Studio, to the instance where the Primary Replica for your Availability Group is active. Expand your group as shown.
Now right-click on “Availability Group Listeners”, and choose the “Add Listener” option.
A new window will be opened. Fill the “Listener DNS Name” with the name of your AD Computer Name. In our case SQLSRVAG01. In the “Port” field, choose an adequate port, something that is not being used.
Now in the “Network Mode”, choose the “Static IP” option, and click in the “Add” button in the bottom right of the window.
Anther window will pop, and it’s time to set the IP of our listener.
Select the proper subnet, related to your IP and fill the IPv4 Address.
Now, click ok in this window, and in the remaining one. And this is what you will have:
In the failover cluster perspective, this is how it looks like:
Notice the dependence between the computer name and the virtual IP. As we are looking to the Failover Cluster Manager now, is a good time to say that, even being feasible to do changes from the Failover Cluster Manager, don’t do this! All the changes need to be made from the SQL Server side, or rephrasing, all the changes must be made from the SQL Server Management Studio! Otherwise you risk to break your configuration.
Testing the connectivity
Now that our listener is online and listening, we can check if it is working fine. First let’s see if the connectivity from the application server is opened, using the famous Telnet Client. In Windows, go to “Run” and write “cmd”, press enter.
Now, in the command line, write the following:
“telnet <your listener name> <listener port>”
As bellow:
If you execute the command and see the following (empty black screen), that means that you were able to connect!! So all good.
If your result is like the following, something is wrong:
In that case, check if you didn’t misspell something (the hostname or the port), if all looks good, you might check the Windows Firewall, if enabled, and add the exception for this IP/Port. Another try is use the listener IP, instead f the computer name. If the connection using the IP works, you need to check why the name resolution is not working. Talk with the Domain/DNS/System Administrators in order to troubleshoot that.
If all looks good on Windows point of view, and if the instance/listener are really only and should be replying, you need to check with the Networking team of your company, on order to have this unlocked. Always pas to them the following information:
- Source IP and hostname (from where you are connecting).
- Destination IP and hostname.
- Destination port.
In the second part of this article, we explained the basic concepts of an Availability Group listener, checked what are the prerequisites, how to actually create a listener using a computer name and a static virtual IP and finally how to test if our application server has connectivity to our newly created access point to the availability groups. Keep tuned, as this series is still not done!
Previous articles in this series:
- AlwaysOn Availability Groups – How to setup AG between a clustered and standalone instance (Part 1)
- AlwaysOn Availability Groups – How to setup AG between a clustered and standalone instance (Part 2)
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