How to Configure Multiple availability groups with separate SQL Listener (SQL 2016 and above) in Azure Always-on VM setup

Anshul Farkya 131

Hi Team,

I would like to configure Multiple availability groups with separate SQL Listener for each AG (SQL 2016 and above) in Azure Always-on VM setup. My AG setup contains two nodes in one region and one node as DR node in another region.

I would like to use only SQL listener name and default port for connection using application connection string.

Please guide me for same.

1 answer

vipullag-MSFT 26,306 Reputation points

2022-02-23T02:19:23.437+00:00

@Anonymous

Welcome to Microsoft Q&A Platform, thanks for posting your query here.

Please consider using multi-subnet AG to setup your scenario. Refer this document once, Simplify Azure SQL Virtual Machines HA and DR configuration by adopting multi subnet approach. AG replicas on different regions are supported by this approach.

The default port for listener will be ok, however the recommendation is to use a different port number – Looking for “WITH IP” in CREATE AVAILABILITY GROUP (Transact-SQL) - SQL Server.

Hope this helps.
Please 'Accept as answer' if the provided information is helpful, so that it can help others in the community looking for help on similar topics.
Please sign in to rate this answer.
Anshul Farkya 131 Reputation points

2022-02-23T07:44:24.363+00:00

Thanks @vipullag-MSFT !

I managed to configure SQL AG group and SQL listener, by reserving secondary IP's through Azure VM NIC. (One IP reserved on one of the two Azure VM's available in same region and one IP reserved on the node in DR region). SQL AG group and failovers are working fine.

However, I am facing below issue connecting using SQL listener name, after failing over to DR node. It works fine when I am connecting locally using SSMS and SQL listener name from DR node.
"TITLE: Connect to Server

------------------------------

Cannot connect to SQL_XXXXX.

------------------------------

ADDITIONAL INFORMATION:

The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

"

Do we need to change the values for "RegisterAllprovidersIP" and "HostRecordTTL" cluster parameters in case of multisubnet scenario, while using secondary IP's for listener configuration?
Any other post configuration steps required after configuring SQL listener using Secondary IP's?

Thanks,
Anshul

vipullag-MSFT 26,306 Reputation points

2022-03-03T04:49:00.727+00:00

@Anonymous

From the information that you shared, not really sure why listener name cannot be connected after failing over to the DR replica. Regarding RegisterAllProvidersIP, you don’t need to manually configure it. It should be auto configured by AG based on MultiSubnetFailover is in the client connect string or not (Check “RegisterAllProvidersIP Setting” section in this document).

Also, please consider below steps for checking listener configurations:

Check the listener configuration and make sure it has one IP for each subnet

Check the connection string is using MultiSubnetFailover=true;

Check the cluster configuration that RegisterAllProvidersIP is set to 1.

Get the output of nslookup or other DNS lookup tool to verify IP addresses are registered properly

Internal team has tested this to cross check, followed the network peering configuration steps in this document and configured an AG across two regions (West Central US – 2 replica, East US – 1 replica). AG listener connection still worked well after failover from one region to another. Configured the AG with SSMS, and no additional WSFC settings need to be customized. Please ensure the network peering is properly configured too.

vipullag-MSFT 26,306 Reputation points

2022-03-03T04:50:15.043+00:00

@Anonymous

Did you face the same issue when failing over between replicas on the same region?

If the root cause cannot be found, requesting you to open a support case for troubleshooting as a regular multi-subnet AG listener configuration.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to Configure Multiple availability groups with separate SQL Listener (SQL 2016 and above) in Azure Always-on VM setup

1 answer

Your answer