Configuring Read-Only Routing and load-balancing across Read-Only replicas

With the arrival of AlwaysOn Availability Group in SQL Server 2012, implementing HA+DR solutions have been an easier and not expensive task in comparison to legacy architectures such as Database Mirroring for HA and Log Shipping for DR, and FCI for HA and Database Mirroring for DR. Nevertheless, at the beginning not everyone has been fully aware of all the power of this technology so that some might not have made the most out of it. Naturally, this technology has been improved over the years, for instance, load-balancing across readable secondary replicas was added, and today in this post, I am coming with a script to configure it.

To begin with, bear in mind that client connectivity to availability group databases is established through its Listener which is a Client Access Point Clustered Resource. This Listener consists of a DNS name, one or more IP address and a Port, and it makes possible that standard connections are routed to the primary replica for Read-Write operations, whereas Read-Only connections are routed to the secondary replicas (via the Read-Only Routing list). More specifically, working with a Listener has the following advantages: it connects to the primary replica (Read-Write), routes Read-Only intent connections to secondary replicas (Read-Only), and acts as a multiple subnet Listener for stretch cluster, multi-site, remote site. However, the client connection behaviour is determined by Availability Group Replica options. These options determine whether a replica is enabled for Read-Only access when in a secondary role and which clients can connect to it, for instance, we can configure secondary replicas to accept no connections at all, only Read-Only connections, or all connections.

Talking a bit more about Read-Only Routing list used by the AG Listener, Read-Only connections are routed to a readable secondary based on Read-Only Routing list which enables automatic direction of client connection to new readable secondary, and even more, it is possible for connections to go to different readable secondaries if available to balance Read-Only access (since SQL2016). In the following example, we have an Availability Group MSSQLAG1with four replicas: NODE1, NODE2, NODE3, NODE4 (inside the DBA300 domain). So, to allow applications to be routed to readable secondary replicas we must configure the Read-Only Routing list. Firtly, starting with the configuration of the Read-Only Routing URL for each replica, in other words, set the URL for each replica. This setting is only used when the local replica is acting as secondary.

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE1'

WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://NODE1.DBA300.COM:1433'))

GO

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE2'

WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://NODE2.DBA300.COM:1433'))

GO

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE3'

WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://NODE3.DBA300.COM:1433'))

GO

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE4'

WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://NODE4.DBA300.COM:1433'))

Now we must include each replica in the Read-Only Routing List. This setting for each replica is used when the replica is acting as primary. In this case,  Read-Only Routing list will always direct traffic to the first available replica in the routing list. For instance, taking the first part of the code, we are saying that when NODE1 is a primary replica then all Read-Only intent connections must be directed to NODE2, but if NODE2 is not available then redirect them to NODE3, otherwise to NODE4.

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE1'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('NODE2', 'NODE3', 'NODE4')))

GO

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE2'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('NODE1', 'NODE3', 'NODE4')))

GO

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE3'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('NODE1', 'NODE2', 'NODE4')))

GO

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE4'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('NODE1', 'NODE2', 'NODE3')))

But if you want to have load-balancing across read-only replicas, you must execute the following code instead of the previous one. For instance, when NODE1 is running under the primary role, all Read-Only intent connections must be directed to NODE2 and NODE3, but if these replicas are not available then these connections should be directed to NODE4. 

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE1'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('NODE2', 'NODE3'), 'NODE4')))

GO

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE2'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('NODE1', 'NODE3'), 'NODE4')))

GO

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE3'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('NODE1', 'NODE2'), 'NODE4')))

GO

ALTER AVAILABILITY GROUP MSSQLAG1

MODIFY REPLICA ON 'NODE4'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('NODE1', 'NODE2'), 'NODE3')))

Maybe you are wondering how we can configure an application to connect to secondary replicas to read data only. Well, after configuring the Read-Only Routing list, we must specify the AG Listener Virtual Name plus "ApplicationIntent=ReadOnly" in the connection string. Furthermore, we can also try it out via SSMS by including the additional parameter “ApplicationIntent=ReadOnly” in the "Additional Connection Parameter" tab box while logging in (click on "options" button to see that box), for more info you can check my previous post about trying out Read-Only intent connections to secondary replicas. That is all for now. I hope you find this post helpful. Let me know any remarks you may have. Stay tuned.