HST Techno

Kamran Hussain

Download Complete Steps

SQL Server Read-Only Routing is a feature used with Always on Availability Groups that allows SQL Server to automatically route read-only queries (like SELECT statements) to a readable secondary replica instead of the primary.

In Simple Words:

When your application sends a read-only request, SQL Server can redirect it to a secondary server, reducing the load on the primary server (which handles both reads and writes). This helps improve performance and scalability.

How It Works:

1. Your application connects using a read-only intent connection string.
2. SQL Server checks the routing rules in the Availability Group settings.
3. It routes the request to a secondary replica that’s set up for read-only workloads.

Execute on Primary Replica Machine of Database

-- On Primary Replica

ALTER AVAILABILITY GROUP [SQLCL]

MODIFY REPLICA ON N'SQL1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [SQLCL]

MODIFY REPLICA ON N'SQL2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [SQLCL]

MODIFY REPLICA ON N'SQL3' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [SQLCL]

MODIFY REPLICA ON N'SQL1'

WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://SQL1.hst.com:1433'));

ALTER AVAILABILITY GROUP [SQLCL]

MODIFY REPLICA ON N'SQL2'

WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://SQL2.hst.com:1433'));

ALTER AVAILABILITY GROUP [SQLCL]

MODIFY REPLICA ON N'SQL3'

WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://SQL3.hst.com:1433'));

-- When SQL1 is Primary

ALTER AVAILABILITY GROUP [SQLCL]

MODIFY REPLICA ON N'SQL1'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('SQL2', 'SQL3')));

-- When SQL2 is Primary

ALTER AVAILABILITY GROUP [SQLCL]

MODIFY REPLICA ON N'SQL2'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('SQL1', 'SQL3')));

-- When SQL3 is Primary

ALTER AVAILABILITY GROUP [SQLCL]

MODIFY REPLICA ON N'SQL3'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('SQL1', 'SQL2')));

ALTER AVAILABILITY GROUP [SQLCL]

MODIFY REPLICA ON N'SQL2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [SQLCL]

MODIFY REPLICA ON N'SQL3' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

How to Verify

SELECT * FROM sys.availability_read_only_routing_lists;

SELECT * FROM sys.availability_replicas;

Connection String for Application

Server=SQLLI;Database=YourDB;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;