4

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 in Synchronizing 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.

Improve this question
8
  • 2
    You're being successfully read only routed, the problem is either 1) the metadata for the tds endpoint of the read-only replica is incorrect 2) the client can not resolve the dns name given back as specified for the metadata of the read-only replica. Please check that the client can directly connect using the metadata in the dmvs you've already configured and let us know.
    – Sean Gallardy
    Dec 4, 2018 at 21:55
  • There are a number of possible answers and all of them reflect connectivity. Don’t assume the read-only url actually works because it clearly is not. Simple checks like the configuration manager vs Listener configurations, firewall ports vs configuration, to SPN (did we validate each host remotely can be connected?) and more can prevent connections. Also, do realize only synchronized replicas are connectable from the listener.
    – clifton_h
    Dec 4, 2018 at 23:32
  • @SeanGallardy, can you give me a little more detail on how exactly I would check that? That went a bit over my head.
    – RollingDBA
    Dec 5, 2018 at 14:08
  • @clifton_h, I can connect to each instance using its specific name, and when I connect to the listener, it does route me to the primary instance just fine; it's only when I try to use ROR as described above that it give me a connection error. Is that what you were asking? Re: synchronized replicas, yes, the replica I'm trying to connect to is in Synchronized status.
    – RollingDBA
    Dec 5, 2018 at 14:11
  • 2
    Would you put (obfuscated names, of course) the output from the read_only_routing_url column in sys.availability_replicas? What I'm saying is, you're getting read only routed, the url is given back to the client, the client reconnects to the url but whatever that url is doesn't seem to be correct or the client is having connectivity issues to that url.
    – Sean Gallardy
    Dec 5, 2018 at 17:11

3 Answers 3

Reset to default
2

The human element was the problem, as usual. Turns out I had accidentally used the primary replica's server name in the Read-Only Routing URLs for all three replicas. There being only one character difference between each name made it difficult to spot, even though I'd looked at them dozens of times.

Thanks to @SeanGallardy for prompting me to pull the URLs, which caused me to notice the problem.

Thanks to @clifton_h for also making a good-faith effort to help. Y'all good people!

Improve this answer
0

Is the listener configured to use port 1433? If not, you'll need to specify the port in the connection string.

Improve this answer
1
  • The listener is using port 1433. That connection seems to be working fine, since I can connect to it successfully when not setting ApplicationIntent=ReadOnly and selecting an AG database.
    – RollingDBA
    Dec 4, 2018 at 21:58
0

I had the same problem with mine, I eventually managed to get it working by using machinename.local in the FQDN instead.

Improve this answer

Your Answer

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

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