1

I have a client with a Server Availability Group. Let's say the AG listener is named AG1 . The AG1 listener is set to static IP with two IP address. When I do a nslookup of AG1, the DNS entry has the same IP addresses. It's an active/passive AG with automatic failover and it hasn't failed over.

If I open a command prompt and ping AG1 , sometimes it succeeds (primary ip replies) and sometimes it fails (secondary ip address is unreachable). In other words, the "Ping AG1" command reply sometimes tries to ping the IP address of the Primary, and in that case it succeeds. Other times the "Ping AG1" command sometimes tries to ping the IP address of the secondary, and in that case it fails. It seems like it switches back and forth every 30 seconds or so.

Is this normal behavior? I would expect the primary to always respond , but perhaps ping isn't intended to work with an AG?

Update the AG group seems to be setup like the pics in this documentation https://docs.aws.amazon.com/quickstart/latest/sql/step3.html where the AG listener's Network Mode is Static IP with two IP addresses

0

2 Answers 2

4

The AG1 listener is set to static IP with two IP address ... If I open a command prompt and ping AG1 , sometimes it succeeds (primary ip replies) and sometimes it fails (secondary ip address is unreachable)

Yes. This is normal when the nodes are on different subnets. The listener requires two separate IP addresses, only one of which is ever online.

Ping will resolve the DNS name, but will attempt to connect only to whichever IP is returned first. The order address are returned varies based on the client's subnet, and through "DNS Round-Robin".

The current SQL Server client libraries are coded to understand that when a DNS name resolves to two IP addresses, it must try both, as only one will be online.

3
0

You need to add MultiSubnetFailover=TRUE to your connection strings.

When you create a listener using SSMS, it sets the cluster parameters RegisterAllProvidersIP to 1 and HostRecordTTL to 1200. You can use PowerShell to change these values to 0 and 300 respectively to avoid that issue.

Change cluster values. Run the below commands in PowerShell.

Import-Module FailoverClusters  
Get-ClusterResource

This will give you the listener value, which is usually of the form AGNAME_LISTENERNAME.

Replace the xx and yy as needed and then run the following steps (the stop/start at the end will cause a brief disconnect to the AG databases):

Get-ClusterResource xx_yy | Set-ClusterParameter RegisterAllProvidersIP 0  
Get-ClusterResource xx_yy | Set-ClusterParameter HostRecordTTL 300  
Stop-ClusterResource xx_yy  
Start-ClusterResource xx_yy  
Start-Clustergroup xx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.