Intermittent timeouts connecting to Availability Group Listener

Question

I have a database in a SQL Server Availability Group running on a database server.

If I log into my web server and connect to the primary database directly by the database server IP the connection is instantaneous every time.

If I instead connect to the primary database by the Availability Group Listener name or the IP for the Availability Group Listener it will connect instantaneously 1 out of every 4 times and timeout the other 3. The instantaneous vs timeout seems to be random.

We're running SQL Server 2014 on Azure VMs if that is related. I'm 8+ hours in, so any help would be greatly appreciated!

did you remember to create the internal load balancer with "direct server return" checked? — Bruno Faria, Jul 19 '16 at 11:44
The internal load balancer is set with "direct server return" checked, so I don't think that is it. Thanks for the suggestion. — NorthFork, Jul 19 '16 at 14:50
Both servers can be accessed directly and working through their own IP, right? — Bruno Faria, Jul 19 '16 at 16:27

score 1 · Answer 1 · answered Jul 19 '16 at 18:27

It appears that the issue turned out to be DNS related. When I changed the Availability Group Endpoint URL to be the internal IP address instead of the FQDN, the problem was resolved. So for anyone else who hits this, that is something to try (could apply to read only routing as well). A couple SQL statements that might help:

Query Current Settings

SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is", rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",  ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url ,*
FROM sys.availability_read_only_routing_lists rl inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id inner join sys.availability_groups ag on ar.group_id = ag.group_id ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

Update Endpoint URL

ALTER AVAILABILITY GROUP [AvailabilityGroupName] 
MODIFY REPLICA ON 'ReplicaName' WITH (ENDPOINT_URL = 'TCP://<Internal IP>:<Port>')

I'm not yet sure why this was intermittent or why my network's DNS wasn't resolving this to an internal address and instead it was being picked up by a wildcard Route53 DNS CNAME record for the domain. If I figure that out, I'll post it just in case it's helpful. Thanks Bruno for the help earlier!

Intermittent timeouts connecting to Availability Group Listener

1 Answers1