always on ha availabity group force read only connection without specifying ApplicationIntent=ReadOnly

Question

We have an AlwaysOn HA sql cluster set up, with read-only replicas in place, and read-only routing in place. We know how to properly route a connection to the read only replica (by specifying ApplicationIntent=ReadOnly connection property).

The problem: we have a "dumb client" from a vendor that queries our database hard for reporting purposes. We'd love to direct its connections to the read only secondary sql server and take load of our primary. But, the connection string properties can not be configured. We can only specify IP/Port/Creds. The vendor said it's not possible to specify connection string properties.

So is there any other way to force connections to query the read-only secondary server? I'm open to creative solutions like a proxy, automatic fail-back, etc.

Answer 1

I can't think of a way to do this automatically, but if it's just one client, then I would do this:

• Create (or have systems create) a C-Name in AD, pointing at the secondary server (not the listener, the actual server that's the secondary).
• Have the client connect using the C-Name to connect.

You will need to manually update the C-Name whenever a failover event happens, but those should be rare or planned?

You could automate this by having a script periodically check which is the primary and then have it update the etc/hosts file. But I would go the manual route if it's just for one...