question

Sai-0641 avatar image
0 Votes"
Sai-0641 asked Sai-0641 edited

Facing connectivity issues with SQL Server 2017 Availability Groups multisubnet IP addresses

Hi Team,

Need urgent help!

We have sql server 2017 always on windows failover cluster. It has 4 nodes, 2 nodes from DC1 (Node1 is primary) and 2 nodes from DC2. Created a listener with multisubnet ip addresses.

The problem is when clients trying to connect listener, it it connecting to multiple addresses. sometimes connecting online IP address sometimes connecting to offline IP address.

How to resolve connectivity issues with SQL Server 2017 Availability Groups.

Thanks in Advance!

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Sai-0641 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @Sai-0641,


Quote from this MS doc:
Static IP
The listener will use one or more static IP addresses. Additional IP addresses are optional. To create an availability group listener across multiple subnets, for each subnet you must specify a static IP address in the listener configuration. Contact your network administrator to get these static IP addresses.


Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sai-0641 avatar image
0 Votes"
Sai-0641 answered

Hi Seeya,

The problem is Application Latency and Timeout issues in Multi-Subnet. Presently SQL listener has two IP addresses. primary DC1- online and secondary Dc2-offline ip address. when client was trying to connect sql server using listener, they were not able to connect due to offline IP address. sometime DNS picking online IP address but some time offline IP addresses.

I have read the below article:
https://wordsontech.com/fixing-application-latency-and-timeout-issues-in-multi-subnet-always-on-sql-server-setup/

Presently cluster has RegisterAllProvidersIP =1 and HostRecordTTL=1200 values.

I want to change RegisterAllProvidersIP =0 but how to decide HostRecordTTL for my environment.

Need your help to decide HostRecordTTL for my 4 node cluster multisubnet environment.


Thanks in Advance!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered Sai-0641 edited

Hi @Sai-0641,

Parameter HostRecordTTL should be set with a value 60 to 300 while the default value is 1200 (20 Minutes).
HostRecordTTL parameter decides how long in seconds the Client operating system will query the DNS for the current IP address. Reducing this value can have an adverse effect on your DNS server if there are several servers connecting to the DNS to resolve the Listener IP Address. Hence it is advised as 60 to 300.
The specific value requires thorough testing to determine the right value for a particular environment.


Best regards,
Seeya


If the response is helpful, please click "[Accept Answer][1]" and upvote it.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Is there any reason to set HostRecordTTL value 60 to 300 only?

0 Votes 0 ·

Hi @Sai-0641,

value 60 to 300 only?

Not ONLY. This is only a suggestion.
The HostRecordTTL setting controls the time to live (TTL) of cached DNS entries. The default value is 1200 seconds, which is too long. A shorter time (1-5 minutes) will make the cached value expire faster. In the case of failover, the client system can resolve the new IP faster. BTW, 60 (1 minute)-300 (5 minutes) is a suitable value, neither long nor short.

Here is a MS example:
https://docs.microsoft.com/en-us/powershell/module/failoverclusters/set-clusterparameter?view=windowsserver2019-ps#example-1
As you can see from the example, Microsoft is also set to 300. But this is not certain, you can determine it according to your actual environment.


Best regards,
Seeya
0 Votes 0 ·

Hi Seeya,

This weekend I am going to implement the below changes using powershell commands in Sql server 2017.
1. Change the RegisterAllProvidersIP parameter.
RegisterAllProvidersIP parameter value is 0
2. Reduce the HostRecordTTL.
HostRecordTTL paramenter value is 300
3. Take the listener offline.
4. Bring the listener back online.
5. Ensure the entire AG is online.

But after changing parameters values how to bring Listener offline and online. and AG offline and online
Could you please guide me with the steps?


0 Votes 0 ·