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


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.

  • 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? – ktsrg Jul 9 '18 at 10:07
  • 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. – Henrico Bekker Jul 9 '18 at 11:04
  • @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. – Dan Guzman Jul 9 '18 at 11:04

No, you cannot route read-only connection request to readable-secondary without ApplicationIntent=ReadOnly connection parameter.

However, you can connect to readable secondary directly without using listener, but as it is mentioned on the other 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. I hope it helps!

For configuring read-only routing, check this page. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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