6

I am in the process of configuring an Availability Group in SQL Server 2019 on Windows Server 2019.

We have two Windows Servers, UT01 and UT02, configured with a single network adapter, using static IP addresses on an Active Directory domain.

The networking group assigned a static IP address for the cluster Virtual Computer Object, UTCL, 192.168.0.12. They also assigned a static IP address for the Availability Group Listener, UTAG, 192.168.0.13.

Server IP Address subnet
UT01 192.168.0.10 192.168.0.0/24
UT02 192.168.0.11 192.168.0.0/24
UTCL 192.168.0.12 192.168.0.0/24
UTAG 192.168.0.13 192.168.0.0/24

The server team configured with Windows Server Failover Cluster, and I configured the SQL Servers and the Availability Group. Both SQL Servers are configured as named instances; so UT01\INS and UT02\INS are the names returned by @@SERVERNAME;

From any machine on the local subnet (192.168.0.0/24) I can connect to the Availability Group using sqlcmd as such:

sqlcmd -S UTAG\INS

When I run PRINT @@SERVERNAME;, either UT01\INS or UT02\INS is returned, depending on which node is currently running the primary node of the Availability Group.

I can connect to the individual SQL Servers from a network outside the local subnet, via a router, using sqlcmd, i.e. sqlcmd -S UT01\INS and sqlcmd -S UT02\INS work correctly, returning the appropriate instance name for @@SERVERNAME.

All good so far, and completely as expected.

However, when I attempt to connect to the AG name via a non-local subnet, the AG only responds when UT01 is running the primary node. When UT02 is running the primary node, we get the typical connectivity error:

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

SQL Server Error Log on UT02 shows no failed logins (it is configured with the default to audit failed logins).

ping UTAG returns the correct IP address, 192.168.0.13, regardless of where I run it. ping UTAG works correctly from the local subnet, and works from remote subnets when the AG is running on UT01, but not when UT02 is the primary.

I installed WireShark on both UT01 and UT02 to see if I can determine what is happening. I filtered the output of WireShark:

((tcp.port == 2136) || (udp.port == 1434)) && ((ip.dst == 192.168.0.13 || ip.src == 192.168.0.13))

The SQL Server AG listener port is configured to listen on port 2136, and both instances are also configured to listen on port 2136. SQL Server Configuration Manager shows Network Configuration is configured to listen on all IP addresses.

Watching WireShark on UT01, when attempting to connect to the AG from a client located on another subnet shows incoming TCP traffic on port 2136 and UDP traffic on port 1434, regardless of which node is the AG primary. When the AG primary is running on UT01, connecting to the AG works, when the AG primary is on UT02, connecting to the AG fails.

Both servers are virtual machines, running on separate physical hosts. The switch (virtual or otherwise) is clearly aware of which node is running the primary since ping UTAG from the local subnet works regardless of which node owns the UTAG IP address. ping UTAG from any other subnet only responds when the AG is running on UT01.

Any ideas?

1

1 Answer 1

4

So, "GARP-based detection" needed to be enabled in the network switch serving the local subnet connecting the two SQL Servers with the other subnets.

GARP is Gratuitous Address Resolution Protocol, which is used to broadcast the MAC address of the network adapter hosting the Availability Group. In our case, the router connected to the switch was not seeing the ARP packets that are automatically generated whenever an Availability Group failover occurs, preventing it from sending TCP packets destined to the AG to the correct MAC address - it was simply always sending those packets to the first node.

This Microsoft page explains the issue.

1
  • 1
    It really only affects same subnet, which you had. A multi-subnet setup where each node was in its own subnet would not be affected (this is how AWS requires their AGs, Azure has this option and ILB as well) due to disabling these types of messages, among others. Jan 12, 2023 at 12:09

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.