Availability databases hosted on SQL Server Always On Availability Groups (AG) can be connected using a unique Virtual Network Name (VNN), called the Availability Group Listener. When an Availability Group is enabled, clients can connect to databases in both primary and secondary replicas without explicitly specifying the SQL Server instance name. You don’t even need to know the instance name to connect to an AG.

When you have configured read-only routing for secondary replicas and the application or client connection contains the application intent set to read-only, the listener will redirect the connection to readable secondary replicas, Otherwise, the listener redirects both read-write and read-only intent connections to the primary replica. If there is a failover, the listener will redirect connections to the new primary and secondary connections based on the application intent.

The Availability Group Listener consist of the following objects:

• a Domain Name System (DNS) name
• a Listener port
• One or more IP addresses (VIP)

For example, you might have these values:

• DNS: salesag.mscorp.com
• Port: 1433
• IP: static or DHCP

The Listener is always owned by the SQL Server instance where the primary replica resides. At the time of failover, the new primary replica will own the listener.

Configuring the Listener

The Availability Group Listener can be created while creating the AG, or it can be created afterward. The Listener creation options are available on the Specify Replicas window, under the Listener tab.

If you want to skip the Listener creation while creating Availability Group, you can leave the default option, Do not create an availability group listener now, selected. Check the below image for reference:

After creating the Availability Group, you can configure the listener from Availability Groups -> Group Name -> Add Listener in SSMS. Either in the create an availability group listener or the New Availability Group Listener wizards, specify the Listener DNS Name, Port, and Network Mode values. The following figure shows the example availability group listener from my lab.

You can configure the Network Mode as DHCP (Dynamic Host Configuration Protocol). DHCP is limited to single subnet, and it is not recommended for production environments. Choose the Static IP option from the drop down if you want a multi-subnet Availability Group.

To add an IP address, click on the Add button and provide the IP address in the Add IPv4 Address text box.

Additionally, you can script out the changes to a query window and see T-SQL script. The code from my demo cluster are shown here:

USE [master]
GO
ALTER AVAILABILITY GROUP [MSCORPAG]
ADD LISTENER N'mscorpag' (
WITH IP
((N'192.168.0.7', N'255.255.252.0')
)
, PORT=1433);
GO

You can configure port 1433, the default SQL Server instance port, for the Availability Group Listener. In that case, you don’t have to specify the port number explicitly in a connection string or client connection. If you specify a custom port, the client must explicitly specify the port. If you have more than one SQL Server instance on the machine, I recommend you use a different port because the listener may be configured to the named instance, which is often listening on a different port.

Connecting to the Availability Group Listener

To connect to a database in an AG through the Availability Group Listener, specify the listener name in the server name box as shown below:

I am using the default port number in the dialog above. If you are using a different port number, specify it explicitly in the connection string by including it after the name, preceded by a comma. An example connection string with a different port number (3306) is provided below:

connUrl = "jdbc:sqlserver://mscorpag.mscorp.com:3306;databaseName=msdb;user=rathish;password=pwd";

Listener and User Permissions

When you create an Availability Group Listener, the cluster will create a computer object on a domain controller and assign its computer name as the virtual network name automatically. To perform this, the cluster should have the Create Computer Object permission in your Active Directory.

Alternatively, I created a listener with the help of a Domain Administrator by following this method. First, create a computer object in Active Directory and assign the listener name as the computer name to that object. The cluster, the name of the cluster that owns the AG, will have the full control on the newly created computer object. Add an entry in DNS to link that computer name with a static IP address. Now create the listener on the AG. In the troubleshooting section of this article, we look into the common error scenarios with listener creation permission.

On SQL Server, you need sysadmin privilege to create the availability group listener and at least the ALTER AVAILABILITY GROUP permission to modify the listener.

Read-Only Routing and the Availability Group Listener

In a SQL Server Availability Group (AG), the read-only routing feature provides scalability by redirecting read-only connections (SELECT queries) to readable secondary replicas. The routing of these connection to secondary replicas works only when the applications or clients connecting availability databases through availability group listener. If you are connecting directly to SQL Server instance name, at the time of failover, connections will not automatically failover to new primary server.

As stated earlier, listener will redirect connections to both primary and secondary replicas. If you have configured any of the secondary replicas as readable and specify the application intent in your connection string as read only, the listener will redirect connections to the secondary readable replicas based on the read-only routing configuration. Refer here for a detailed explanation. An example connection string with the Application Intent and default database set is given below:

connUrl = "jdbc:sqlserver://mscorpag.mscorp.com:3306;databaseName=mscorp;user=rathish;password=pwd;ApplicationIntent=ReadOnly";

You must specify the ApplicationIntent and Default database in the connection string for read-only routing to work, otherwise the connection will be redirected to the primary replica only. Later in this article, we will see the troubleshooting steps for this scenario.

Availability Group Listener and Multi-Subnet Failover

You should set the MultiSubnetFailover option as True to enable this option and provide faster failover when your AG spans multiple subnets. It is recommended to set this option as True, even if the AG only spans a single subnet. This provides additional optimizations, even for single subnets at the time of failover.

An example connection string with the multi-subnet failover option:

connUrl = "jdbc:sqlserver://mscorpag.mscorp.com:3306;databaseName=mscorp;user=rathish;password=pwd;ApplicationIntent=ReadOnly;MultiSubnetFailover=True";

Monitoring the Availability Group Listener

SQL Server provides the following catalog views to monitor availability group listener properties:

• sys.availability_group_listener_ip_addresses: returns the virtual IP address of availability group listeners
• sys.availability_group_listeners: returns the network name of availability group listeners
• sys.dm_tcp_listener_states: returns the status of TCP IP address and port of availability group listener.

Removing the Availability Group Listener

You can remove Availability Group Listener, either using SSMS or T-SQL. In SSMS, right-click on listener name and select the Delete options to remove the availability group listener.

Using T-SQL, use an ALTER command such as the one below:

ALTER AVAILABILITY GROUP MSCORPAG REMOVE LISTENER 'mscorpag.mscorp.net';

Troubleshooting availability group listener issues

Here are a few sample scenarios that might occur along with a few solutions.

Scenario 1: Unable to create availability group listener

Error: Microsoft SQL Server, Error: 19457

Message:

The specified IP Address '<IP address>' is not valid in the cluster-allowed IP range. Check with the network administrator to select values that are appropriate for the cluster-allowed IP range. (Microsoft SQL Server, Error: 19457)

Msg 19471, Level 16, State 0, Line 2 
The WSFC cluster could not bring the Network Name resource with DNS name '<DNS name>' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

Msg 19476, Level 16, State 4, Line 2 
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator.

Possible Cause 1: The cluster name account does not have the Create Computer Objects in Active Directory Organizational Unit.

Solution: Grant Create Computer Object to Cluster account name.

Select the Advanced Feature option by View -> Advanced Features:

Go to Advanced Security Settings for Organizational Unit (OU).  Follow the highlighted part as below image:

Select the Create Computer Objects from permission tab:

Possible Cause 2: The cluster user account, which has Account Operator permission on Active Directory by default can create up to 10 computer objects, and it has exceeded its limit now.

Solution: If your organisation polity allowed, grant Create Computer Object permission to cluster account, or pre-stage the required objects on the Active Directory, organizational unit.

When you create an Availability Group Listener, a virtual computer object will be created at the particular organizational unit of active directory automatically. You can pre-stage the virtual computer objects as follows:

Create a computer objects under the organizational unit where you hosted the cluster. Check with your domain administrator if you don’t have access to create objects on Active Directory Domain Controller.

Grant Full control permission to cluster name account on newly created computer object. Note that, this name should be same as the listener name, you will be creating on the AG.

Scenario 2: Read-Only routing not working with listener

Error: ApplicationIntent=ReadOnly is specified in the application connection string and read-only routing is configured on the AG, but when connecting to listener, it is not redirecting to secondary readable replicas.

Possible Cause: You have not mentioned the default database in the connection string. For more information on this behavior, click here.

Solution: Add Initial Catalog value in connection string.

Example SSMS connection string:

Scenario 3: Availability Group Listener - login time-out error

Error: You are unable to connect to availability group listener in a multi-subnet environment. This error usually occurs at the time of failover.

Possible Cause 1: Your application uses legacy data provider that does not support the multi-subnet failover features.

Solution: Use the newer version of SQLClient drivers that supports multi-subnet features.

Possible Cause 2: MultiSubnetFailover parameters are not used in the connection string.

Solution: Include the MultiSubnetFailover=True parameter in connection string to fix this issue.

Example:

connUrl = "jdbc:sqlserver://mscorpag.mscorp.com:3306;databaseName=mscorp;user=rathish;password=pwd;ApplicationIntent=ReadOnly;MultiSubnetFailover=True";

Scenario 3: Availability Group Listener name not resolving to IP address

Error: After failover in a multi-subnet environment, ping command from client not resolving to new IP address of the listener and DNS entry of the listener name shows IPs of both subnets.

Possible Cause: This error generally occurs, when listener is created using the Failover Cluster Manager, rather than from SSMS.

Solution: Set the value of RegisterAllProvidersIP value to 0. This require a restart of the listener network name resource.

Example:

PowerShell:
Import-Module FailoverClusters
Get-ClusterResource dbcluster |Set-ClusterParameter
RegisterAllProvidersIP 0
Cluster.exe:
cluster /cluster: dbcluster res mscorpag /priv
RegisterAllProvidersIP=0

If still ping to listener returning wrong IP address, from application/client system, open a command prompt as administrator and run the ipconfig /flushdns command.

Scenario 4: Server cannot host the availability group listener IP address

Error: Error 19456, Severity 16:

Error 19456, Severity 16: None of the IP addresses configured for the availability group listener can be hosted by the server '%.*ls'. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address which can be hosted.

Solution: Add a new IP address manually to existing listener with different subnet.

Scenario 5: Active Directory Policy

Error: Error 8557

Message:

Message:  Error 8557 (Your computer could not be joined to the domain. You have exceeded the maximum number of computer accounts you are allowed to create in this domain. Contact your system administrator to have this limit reset or increased.)

A computer object is created automatically whenever you create an Availability Group Listener. Even if you drop the listener, the computer object remains there in Active Directory. By default, authenticated domain users can create up to 10 computer objects. When this limit is exceeded, it will throw an error at the time of the Availability Group Listener creation.

Solution: You may contact your domain admin to clean up this computer objects or ask them to increase the default limit (I think it is a global variable).

Best practices

A few good practices to follow when creating the Availability Group Listener.

• Use a static IP address for reliable connections and multi subnet failover
• Use a meaningful and unique listener name for each AG
• Test the Listener on a staging server before implementing it on production environment
• Additionally, to avoid the accidental deletion on cluster objects, enable the Protect objects from accidental deletion option.

I hope this article is helpful to you, if you have queries or want to share your thoughts on availability group listener, please write it in comment section. Thank you!

The article also published on my blog, you can visit here.