1

I have no problem with setup "Read-only routing".

My point is if we use "Read-only routing". We need to add the "ApplicationIntent=ReadOnly" into our application connection string. That's beyond our DBA's role.

Is there any other way to let one application such as SAS to connect to the listener and the SQL AG will point it to the secondary?

Many thanks,

  • So long as these aren't Failover Cluster Instances, you can create an additional Client Access Point in the cluster and set its Preferred Owner to the node hosting the secondary. It won't automatically coordinate with the AG Listener to point to the other node, though. Or you can just point the application directly to the secondary instance, and bypass the AG Listener. – David Browne - Microsoft Aug 7 '19 at 16:47
  • Thanks David, but in this case, If we failover the AG, It will not work, right? – DBALUKE HUANG Aug 8 '19 at 16:13
  • The second Client Access Point would still work, but it still be online on the same node, which is hosting the primary replica after a failover. – David Browne - Microsoft Aug 8 '19 at 17:39
2

Is there any other way to let one application such as SAS to connect to the listener and the SQL AG will point it to the secondary?

Unfortunately no, not within SQL Server.

You must specify ApplicationIntent=ReadOnly in the connection string in order for the connection to be eligible for read-only routing, as is documented:

Requirements and Recommendations for Client Connection-Strings

For a client application to use read-only routing, its connection string must satisfy the following requirements:

  • ...
  • Set the application intent attribute/property to readonly.
  • ...
| improve this answer | |

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