April 21, 2021 at 2:53 pm
I have 2 servers which are part of AG. DB1(Primary) and DB2(Secondary). It has been setup like this for quite some time now but we don't have read routing enabled/configured. I went thru GUI and scripted out what I want to do. Couple of question related to my inquiry.
Do I have to failover/reboot when I run my script on the primary server? Is there anything I should be looking out for before running the script?
Looking at the AG property, readable secondary option is set to Yes for a secondary server. Read-Only routing section is blank.
USE [master]
GO
ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON
N'DB1' WITH
(
SECONDARY_ROLE
(
READ_ONLY_ROUTING_URL = N'TCP://db1**********:1433'
)
)
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON
N'DB2' WITH
(
SECONDARY_ROLE
(
READ_ONLY_ROUTING_URL = N'TCP://db2*************:1433'
)
)
GO
ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON
N'DB1' WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST = (N'DB2')
)
)
GO
ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON
N'DB2' WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST = (N'DB1')
)
)
GO
April 21, 2021 at 6:33 pm
Before going down this road - what problem are you trying to resolve by enabling read-routing? If you are having issues with select statements blocking writes there may be other options.
I would not enable read-only routing unless - and only - if we were seeing IO related issues due to lots of reads. That is - lots of statements that are read only and the system is IO bound. For any other scenario there are better methods available to resolve the issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply