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

Sai 126 Reputation points
2021-09-21T09:13:49.693+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,436 Reputation points
    2021-09-22T07:39:03.337+00:00

    Hi @Sai ,

    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.

    0 comments
  2. Sai 126 Reputation points
    2021-09-24T08:33:07.49+00:00

    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!

    0 comments
  3. Seeya Xi-MSFT 16,436 Reputation points
    2021-09-27T08:17:37.767+00:00

    Hi @Sai ,

    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.