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
September 22, 2022 at 7:00 am
You need to check in your application how you do your read connections and write connections.
If you want to offload reads you need to ensure you use two connection strings in your application.
When you want to read you use the ApplicationIntent read only connection.
When you want to write you use the normal connection.
Offloading the reads in the application requires application redesign to cope with the fact you can’t write on a read connection.
Read only routing for an existing application is not something to do lightly, you need to completely redesign the app to cope.
Now for Green field new apps yes as you can write the app to do the new architecture or if the application is for reporting only yes OK.
But it is not something to enable lightly And just expect it to work without changes to applications.
September 22, 2022 at 7:45 am
Thank a ton for quick response. Based on what you said, the application must have 2 connection strings.
-> wherever WRITES are there , they have to connect to the listener without applicationintent=readonly
-> and wherever READS are there, they have to connect to the listener with applicationintent=readonly
However, I am unsure about how much work needs to be done by the application team to do this.
For all this, I am assuming a lot of work needs to be done from application side? Assume there is a stored proc with some combinations of dml + select stmts... in that case, how?
or is there any automated way? I am little confused! or only app team should determine only reporting queries should go to secondary replica?
While researching, I came across this sql server 2019 msdn article. Not sure if this is taken care automatically.
CREATE AVAILABILITY GROUP MyAg
WITH ( CLUSTER_TYPE = NONE )
FOR
DATABASE [<Database1>]
REPLICA ON
'COMPUTER01' WITH
(
ENDPOINT_URL = 'TCP://COMPUTER01.<domain>.<tld>:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL,
READ_ONLY_ROUTING_URL = 'TCP://COMPUTER01.<domain>.<tld>:1433' ),
PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,
READ_ONLY_ROUTING_LIST = ('COMPUTER02', 'COMPUTER03'),
READ_WRITE_ROUTING_URL = 'TCP://COMPUTER01.<domain>.<tld>:1433' ),
SESSION_TIMEOUT = 10
),
'COMPUTER02' WITH
(
ENDPOINT_URL = 'TCP://COMPUTER02.<domain>.<tld>:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL,
READ_ONLY_ROUTING_URL = 'TCP://COMPUTER02.<domain>.<tld>:1433' ),
PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,
READ_ONLY_ROUTING_LIST = ('COMPUTER01', 'COMPUTER03'),
READ_WRITE_ROUTING_URL = 'TCP://COMPUTER02.<domain>.<tld>:1433' ),
SESSION_TIMEOUT = 10
),
'COMPUTER03' WITH
(
ENDPOINT_URL = 'TCP://COMPUTER03.<domain>.<tld>:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL,
READ_ONLY_ROUTING_URL = 'TCP://COMPUTER03.<domain>.<tld>:1433' ),
PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,
READ_ONLY_ROUTING_LIST = ('COMPUTER01', 'COMPUTER02'),
READ_WRITE_ROUTING_URL = 'TCP://COMPUTER03.<domain>.<tld>:1433' ),
SESSION_TIMEOUT = 10
);
GO
There is a lot of work for your application development team to do here.
Every call the the database needs to be checked for what it does.
If that call is a pure read call and the data is then only displayed in the application, and never modified like a report query, then use use ApplicationIntent=ReadOnly
If the call does a read then an insert/update/delete all within the same procedure then it must use a none read only connection.
As I say for existing applications, trying to do read only routing is a lot of technical effort.
Additionally the link you posted is in reference to SQL 2019 or above, but this question has been posted in 2017. What version of SQL are you using here?
It does look like that change in 2019 should route the read_write back to primary if it isn't a pure read_only call, but I would still look at the application level redesign to be sure the right connections go to the right replicas.
September 22, 2022 at 9:31 am
thanks a lot.
We are on sql 2017.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply