How to redirect read queries transparently to Secondary replica in Always on

Question

We have Always ON setup for our database. Users are connecting to our database using listener URL.

We are currently asking users to use ApplicationIntent = ReadOnly to redirect users to secondary replica. But, still many users are connecting to PRIMARY, without changing connection string.

We see that, there is an option of "Allow All connections" to Secondary replica. Will that option take care of automatically routing read queries to secondary replica, even without setting ApplicationIntent = Readonly in the connection string?

We read about it: https://learn.microsoft.com/en-us/archive/blogs/sqlserverstorageengine/alwayson-why-there-are-two-options-to-enable-a-secondary-replica-for-read-workload . But, still not very clear on how it will work.

Can you please tell us, is there a way to implement redirecting read only queries to secondary replica transparently ?

Answer 1

Can you please tell us, is there a way to implement redirecting read only queries to secondary replica transparently ?

Natively in SQL Server AlwaysOn Availability Groups, I don't believe so.

The thing is, a connection needs to be established first before a query can be ran. Once the connection is established to one of the replica servers, then a query can be executed, but it's too late at that point since the user will already be connected to one of the two servers (the primary in your problematic case). There is no redirecting the connection to another server capability, once it's already connected to one of the servers.

And SQL Server can't know what type of query (read vs write) is going to be executed before it is ran, without the user telling the server in the connection string that they plan to run read-only queries, aka ApplicationIntent = ReadOnly. It's all a little bit of a chicken or the egg problem. Even with that connection string parameter, and being connected to a read replica, nothing stops the user from attempting to run a write query, but it'll obviously just error out on read replica. This is actually mentioned in the docs you linked:

The word ‘intent’ is important here as SQL Server makes no application check to guarantee that there are no DDL/DML operations in the application connecting with ‘ReadOnly’ application intent. It assumes that customer will only connect read workloads.