0

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.

1 Answer 1

0

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

1
  • We can configure the client to connect directly to one of the servers in the AG instead of the listener, but the since any server can become primary when the other reboots, we'd need to put monitoring in place to tell us to "manually fail back". Not ideal, but an option
    – bradvido
    Jun 23, 2023 at 18:34

Your Answer

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

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