How to Deal with Connectivity Issues while Working on AlwaysOn SQL Server Groups

This article addresses a simple solution for dealing with connectivity issues while working on AlwaysOn SQL Server groups.

All IT officials have faced certain connectivity problems while working on AlwaysOn SQL Server groups one or more times in their careers. Usually, this connectivity issue is caused as multiple IP addresses are provided at the time of setting up an AG listener for a multi-subnet cluster.

At the time of configuration of AG (Availability Groups) listener, a user needs to ensure to define each subnet’s IP address in their environment. This will automatically create a dependency for each and every subnet’s IP address individually.

When Availability Groups are running, they are registered in the user DNS for each subnet under the Windows Cluster Service. Most organizations use two subnets, there is always a possibility of more depending on the size of the organization. The Window Cluster submits these IP addresses of subnet into the DNS server. Then the Microsoft Window DNS generates a record for the subnet.

Reason for the Connectivity Issue

Note that IP addresses for secondary replica server are offline, while the IP address for a primary replica is online. Most connectivity issues are raised due to these settings, as some IP addresses are offline, while others are online. So the query returned by Microsoft DSN Server can be the one with the offline address. This will automatically send the client to the timeout problem.

Depending on the DNS configuration, a client application can take some time before returning another IP address which might consist of an online or offline IP address. The default connection attempt of TCP however, is only 21 seconds. So a user will have to wait for 21 to connect after an offline server response. This basically works on “trial and error approach” until the user is able to return an online IP address in its client application.

How to Deal with this Connectivity Issue?

Note that when a client connection is made with an AG listener, the Microsoft Window Cluster needs to set an AG name for the IP address which can be resolved by searching Microsoft DNS Server. Then DNS Severs returns with all the registered addresses which were configured during the user’s AG.

In order to solve this intermittent timeout problem, users need to revisit their AG listener properties and make the following changes.

• Steps 1: Open the AG Window. And select the (RegisterAllProviderIP) IP address that is set at 0. During the configuration, some AG listener is set on 0 by default via Windows cluster. This simply signifies that all subnets are going to be registered in DNS server.
• Step 2: When RegisterAllProviderIP’s property will be set to 1, any client whose application doesn’t match the string will face the same connection problem. This issue mostly occurs when clients sequentially attempt to connect multiple registered IP addresses.

Note: If a user wants to take advantage of a multi-subnet feature in AG listener, the application will require a data provider a keyword to support its connection string property.

In contrast, if the user changes RegisterAllProviderIP to 0, it will be registered with WSFC Cluster in Client Access Point as an active IP address, which will also reduce the latency of the requirement of legacy applications.

Despite the best implementation of AlwaysOn Availability groups, the data stored in your database may come under threat during a catastrophic SQL crash. Hence businesses must invest in an mdf repair tool to deal with contingencies.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover access and sql recovery software products. For more information visit https://www.datanumen.com/