Setting up multiple instance read-only routing

Question

Hi All,

I have googled this but unfortunately could not find any answer because everyone has a document that shows only 1 AG/Instance example and none for multiple instances/AG groups an a same server.

I have 3 instances on one server (Always On with 2 node cluster) and I am trying to setup read only routing. All instances are named instances. Here is my script for the first 2 AG and first 2 instances. Am I setting this up correct ?. Is the URL for both needs to be the same ?. Is the read-only routing list, port correct ? Thanks.

It is SQL Server 2019 CU28.

First Instance and AG1

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SERVER1\INST1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))

GO

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SERVER2\INST1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))

GO

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SERVER1\INST1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SERVER1.contoso.com:1433'))

GO

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SERVER2\INST1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SERVER2.contoso.com:1433'))

GO

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SERVER1\INST1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('SERVER2\INST1','SERVER1\INST1')));

GO

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SERVER2\INST1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SERVER1\INST1','SERVER2\INST1')));

GO

Second Instance and AG2

ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SERVER1\INST2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))

GO

ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SERVER2\INST2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))

GO

ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SERVER1\INST2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SERVER1.contoso.com:1433'))

GO

ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SERVER2\INST2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SERVER2.contoso.com:1433'))

GO

ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SERVER1\INST2' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('SERVER2\INST1','SERVER1\INST1')));

GO

ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SERVER2\INST2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SERVER1\INST1','SERVER2\INST1')));

GO

Answer 1

Never mind. Found it. You can't use the Listener port in URL (1433. All 3 listeners using it). You need to use what is assigned to the instance in configuration manager.