SQL Server AG related question

Question

Hi All,

I have question on readonly routing.

We are trying to setup read only routing on AG. Reads are going to secondary but writes are also going to secondary and since secondary all dbs are in readonly mode , writes are failing. not sure if the routing tables is missing anything.

SQL Version

Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64) Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

Regards,

Sam

Answer 1

My question is, does the application team has to use 2 separate connection strings?

Yes. The connection string should not specify read-only intent when writes are performed.

For all this, I am assuming a lot of work needs to be done from application side?

Not necessarily a lot of work, but much depends on application design. For example, if read-only reporting functionality is already segregated in the application code, I would expect the change to use a read-only connection string for report queries to be fairly trivial, especially if the code uses already a connection factory technique to centralize connection management.

OTOH, if read and read-write functionality is co-mingled throughout a large app, there will be additional effort involved to leverage the secondary replica for the read-only workload. One migration strategy would be to change the connection string only for expensive read-only queries initially and follow up with others later. That way, the most costly readd-only queries won't impact the rest of the workload.

Answer 2

Hi @Sam ，

You can refer to this official doc:
Configure read-only routing for an Always On availability group

You must configure one or more availability replicas to accept read-only in the secondary role ( to be readable secondary replicas)

To support read-only routing, you must possess an availability group listener, read-only clients must direct their connection requests to this listener, and the client's connection strings must specify the application intent as "read-only", they must be read-intent connect requests

In short, readable secondary replicas is required

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.