SQL Server ReadOnly Routing Connection String

Question

Can we route read-only connection to readable secondary replica without setting connection string (ApplicationIntent = ReadOnly)?

Answer 1

You would be required to set the Secondary Replica Copy as Readable, different from Read-Intent, and point the connection string to the actual instance where the replica resides.

This is not advisable, as it negates the HA usability of any Read-Intent replicas during failover.

• ktsrg: So without defining "ApplicationIntent = ReadOnly" in connection strings, there's no way that listener automatically routing read-only connection to readable secondary replica and "write connection query" to primary replica?
• Henrico Bekker: Like I said, you will need to use the secondary replica instance then, not the AG Listener name. But no, if you don't mention Intent, it will direct traffic to the ReadWrite Primary Replica if you use the Listener name in your connection string.
• Dan Guzman: @ktsrg, correct. How would the listener know how to route the connection without the intent declared? Without that specified in the connection string, you would need to route connection yourself.

Answer 2

Can we route read-only connection to readable secondary replica without setting connection string (ApplicationIntent = ReadOnly)?

No. However, you can connect to readable secondary directly without using listener, but as it is mentioned in another answer, in case the secondary replica is down, HA is not achievable, in this case.

I could not think of any such specific use case. For configuring read-only routing, see my post.