First off, my apologies if this has already been answered elsewhere. I have not been able to find any articles or threads describing my exact situation, but no one's Google-fu is perfect.
With that out of the way, here's my situation: I have a SQL Server 2017 (14.0.1000.169) Enterprise environment with three AG replicas (one primary, one secondary with synchronous commit, and one secondary with asynchronous commit), and whenever I try to connect to the Listener using ApplicationIntent=ReadOnly
and specifying an AG database, it gives me the following 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.
It connects with no issues if I don't specify the ApplicationIntent
flag, or if I don't specify a database. Here are the things I have already checked:
- Listener is configured, and can be connected to normally.
- Read-Only Routing URLs are configured for each replica, using the FQDN and TCP port for that instance (ran the script provided here to verify that I have those correct).
- Read-Only Routing Lists are configured for each replica.
- The sync secondary is in
Synchronized
status, and the async secondary is inSynchronizing
status. - The secondary replicas are set to allow all connections (
ALLOW_CONNECTIONS = ALL
) and can be queried directly using their own instance names.
Each SQL Server instance that hosts a replica is using a different TCP port from the others for both its Endpoint URL and its Read-Only Routing URL (six different ports between the three instances), and the Listener is listening on a different port from all of those. I don't think this has anything to do with the problem, but I'm mentioning it for the sake of thoroughness.
Like I said above, I haven't found anything useful for my exact situation, since all the results I've been able to find have just been people who haven't set up their routing lists or whatever. Any ideas on next steps here or anything I might be missing? Please let me know if you need any additional info about the environment.