Fix: When an Availability Group listener’s IP is stolen

I ran into an issue with a client last month: we could connect to the individual nodes of a SQL Server Availability Group (AG), but not the AG listener (e.g., connecting to Server1 is ok, but connecting to listener AG_Server returned an error).

At this point, it’s an excellent idea to check if the listener IP is in use elsewhere.

Get the Availability Group listener IP

Use the following query to list all the listeners on your AG, and the IP address and listener state:

select [listener].dns_name as listener_name
 , [listener].ip_configuration_string_from_cluster
 , [ip].ip_address
 , [states].port
 , [states].type_desc
 , [states].state_desc
 , [states].start_time
from sys.availability_group_listeners as [listener]
INNER JOIN sys.availability_group_listener_ip_addresses as [ip]
 ON [listener].listener_id = [ip].listener_id
INNER JOIN sys.dm_tcp_listener_states as [states]
 ON [ip].ip_address = [states].ip_address
WHERE type = 0; /* Transact-SQL, as opposed to Service broker/DB mirroring */

On my AG, this returns something like this:

Now that you’ve found your AG listener IP, check (or get your network person to check) to see if that IP is already in use by something else. If the answer is yes, it is, then…

Resolve

You’d think this would be an easy situation to resolve. Just assign that other thing a different IP, right? It wasn’t so simple for us.

After some mucking around, we found that we had to clear the ARP table of the router, to clear the MAC address of the stolen IP.

That’s the answer. The network data sticks around for a while even after you fix the duplicate IP issue.

This was a quick write-up, so feel free to add clarifications and additions in the comments. Hope this helps!

-J