Configure an Availability Group listener

Go back to DP-300 Tutorials

In this tutorial, we’ll show you how to use SQL Server Management Studio, Transact-SQL, or PowerShell to build or configure a single availability group listener for an Always On availability group in SQL Server 2019. (15.x).

Using SQL Server Management Studio

To create or configure an availability group listener

• Firstly, in Object Explorer, connect to the server instance that hosts the primary replica of the availability group, and click the server name to expand the server tree.
• Secondly, expand the Always On High Availability node and the Availability Groups node.
• Thirdly, click the availability group whose listener you want to configure, and choose one of the following alternatives:
  • To create a listener, right-click the Availability group Listeners node, and select the New Listener command.
  • Next, to change the port number of an existing listener, expand the Availability group Listeners node, right-click the listener, and select the Properties command. Enter the new port number into the Port field, and click OK.

Using Transact-SQL

To create or configure an availability group listener

• Firstly, connect to the server instance that hosts the primary replica.
• Secondly, use the LISTENER option of the CREATE AVAILABILITY GROUP statement or the ADD LISTENER option of the ALTER AVAILABILITY GROUP statement.

The following example adds an availability group listener to an existing availability group named MyAg2. This listener is given a unique DNS name, MyAg2ListenerIvP6. Because the two replicas are on separate subnets, the listener should utilize static IP addresses, as advised. The WITH IP clause, however, provides a static IP address in IPv6 format for each of the two availability replicas, 2001:4898:f0:f00f::cf3c and 2001:4898:e0:f213::4ce2. The optional PORT parameter is also used in this example to provide port 60173 as the listener port.

ALTER AVAILABILITY GROUP MyAg2
ADD LISTENER ‘MyAg2ListenerIvP6’ ( WITH IP ( (‘2001:db88:f0:f00f::cf3c’),(‘2001:4898:e0:f213::4ce2’) ) , PORT = 60173 );
GO

Using PowerShell

To create or configure an availability group listener

• Firstly, change directory (cd) to the server instance that hosts the primary replica.
• Secondly, to create or modify an availability group listener use one of the following cmdlets:

New-SqlAvailabilityGroupListener
Adds a new availability group listener to an existing availability group.

The New-SqlAvailabilityGroupListener command, for example, creates a MyListener availability group listener for the availability group MyAg. As its virtual IP address, this listener will utilize the IPv4 address given to the -StaticIp argument.

New-SqlAvailabilityGroupListener -Name MyListener -StaticIp '192.168.3.1/255.255.252.0'
-Path SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MyAg

Set-SqlAvailabilityGroupListener
Modifies the port setting on an existing availability group listener.

For example, the following Set-SqlAvailabilityGroupListener command sets the port number for the availability group named MyListener to 1535. This port is used to listen for connections to the listener.

Set-SqlAvailabilityGroupListener -Port 1535 `
-Path SQLSERVER:\Sql\PrimaryServer\InstanceName\AvailabilityGroups\MyAg\AGListeners\MyListener

Follow-up: After Creating an Availability Group Listener

MultiSubnetFailover Keyword and Associated Features

MultiSubnetFailover is a new connection string keyword used to enable faster failover with Always On Availability Groups and Always On Failover Cluster Instances in SQL Server 2012. The following three sub-features are enabled when MultiSubnetFailover=True is set in connection string:

• Firstly, faster multi-subnet failover to a multi-subnet listener for an Always On Availability Group or Failover Cluster Instances.
• Secondly, faster single subnet failover to a single subnet listener for an Always On Availability Group or Failover Cluster Instances.
• Lastly, named instance resolution to a multi-subnet Always On Failover Cluster Instance.

MultiSubnetFailover=True Not Supported by NET Framework 3.5 or OLEDB

Issue: If your Availability Group or Failover Cluster Instance has a listener name depending on multiple IP addresses from different subnets. You’re also using SQL Native Client 11.0 OLEDB or ADO.NET with.NET Framework 3.5SP1. The availability grouplistener will then experience a connection timeout for up to 50% of your client connection requests.

Workarounds: We recommend that you do one of the following tasks.

• Firstly, if do not have the permission to manipulate cluster resources, change your connection timeout to 30 seconds.
  • Pros: If a cross-subnet failover occurs, client recovery time is short.
  • Cons: Half of the client connections will take more than 20 seconds
• Secondly, if you have the permission to manipulate cluster resources, the more recommended approach is to set the network name of your availability group listener to RegisterAllProvidersIP=0. For more information, see “RegisterAllProvidersIP Setting” later in this section.
  • Pros: You do not need to increase your client-connection timeout value.
  • Cons: If a cross-subnet failover occurs, the client recovery time could be 15 minutes or longer, depending on your HostRecordTTL setting and the setting of your cross-site DNS/AD replication schedule.

Sample PowerShell Script to Disable RegisterAllProvidersIP and Reduce TTL

The following PowerShell example demonstrates how to configure both the RegisterAllProvidersIP and HostRecordTTL cluster parameters for the listener resource. However, the DNS record will be cached for 5 minutes rather than the default 20 minutes. For legacy clients that cannot utilise the MultiSubnetFailover option, changing both cluster settings may shorten the time it takes to connect to the right IP address following a failover. Replace the name of the listener you’re updating with yourListenerName.

Import-Module FailoverClusters
Get-ClusterResource yourListenerName | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource yourListenerName | Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource yourListenerName
Start-ClusterResource yourListenerName
Start-Clustergroup yourListenerGroupName

Create an Additional Listener for an Availability Group (Optional)

After you create one listener through SQL Server, you can add an additional listener, as follows:

• Firstly, create the listener using either of the following tools:
  • Using WSFC Failover Cluster Manager:
    • Firstly, add a client access point and configure the IP address.
    • Then, bring the listener online.
    • And, add a dependency to the WSFC availability group resource.
  • Next, using Windows PowerShell for failover clusters:
    • Use Add-ClusterResource to create a network name and the IP address resources.
    • Then, use Start-ClusterResource to start the network name resource.
    • Use Add-ClusterResourceDependency to set the dependency between the network name and the existing SQL Server Availability Group resource.
• Secondly, start SQL Server listening on the new listener. After creating the additional listener, connect to the instance of SQL Server that hosts the primary replica of the availability group and use SQL Server Management Studio, Transact-SQL, or PowerShell to modify the listener port.

Reference: Microsoft Documentation

Go back to DP-300 Tutorials