Always on Lisenter connectivity issue!

Karthikeyan Chinnusamy 41 Reputation points
2022-08-17T10:33:36.947+00:00

Hi All,

We have three SQL servers in Always on availability group, two servers are in Primary subnet and other server is in DR subnet. Have configured two subnet IPs for listener configuration and created dns name with both Ips

When we connect from application or ping or ODBC to dns name, most of the time it connects to DR IP and connection fails sometime it connects to primary IP and works fine.

Please let me know how to fix this problem.

Thanks

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
{count} votes

Accepted answer
  1. Dan Guzman 9,206 Reputation points
    2022-08-17T11:36:00.23+00:00

    When we connect from application or ping or ODBC, most of the time it connects to DR IP and connection fails sometime it connects to primary IP and works fine.

    These symptoms suggest RegisterAllProvidersIP is set to 1 (the default) so both listener IP addresses are registered in DNS. With newer ODBC drivers, specify MultiSubnetFailover=True in the connection string so the connection will be made to the active primary node.

    If you are using an older ODBC driver that doesn't support MultiSubnetFailover, change the RegisterAllProvidersIP setting to 0 per the documentation so that only the primary listener IP address is registered.


0 additional answers

Sort by: Most helpful