Availability Group Listeners Across Subnets & Other Tips

When you are setting up an AG listener here are some helpful tips.

If you are setting up the availability group over subnets you will need to add the IP’s for each subnet.

If an error occurs regarding AD permissions when creating the listener make sure the container where the SQL Cluster computer account has been created has the correct permissions as per this article.

If you use a port number other than 1433 make sure you specify the port number in the connection strings. Otherwise although the clients/users may still be able to connect (depending on instance set up and firewall rules) they will be connecting to the instance rather than the listener.

RegisterAllProvidersIP and HostRecordTTL settings

For older clients make sure you change these two settings or you will get lengthy times happening for DNS to update after a failover which will be a very disappointing experience.

listener parameters

If you want to read all the reasons behind changing these settings here it is from the horses mouth.

There are a couple of different ways to set these but by far the easiest is with powershell.

Run this to get a list of resources:

Get-ClusterResources

Then grab the listener name which will be something like ag_myaggroup_ag_mylistener

Change the TTL to something lower like this:

Get-ClusterResource ag_myaggroup_ag_mylistener | Set-ClusterParameter -Name HostRecordTTL -Value 120

Change the RegisterAllProvidersIP to 0:

Get-ClusterResource ag_myaggroup_ag_mylistener | Set-ClusterParameter -Name RegisterAllProvidersIP -Value 0

Check your configuration at any time by running this:
Get-ClusterResource ag_myaggroup_ag_mylistener | Get-ClusterParameter HostRecordTTL, RegisterAllProvidersIP

Note: You need to take the AG offline and bring it back online again for the above changes to take effect.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.