SQL Server 2016 Availability Groups – Load Balancing 101

Introduction

SQL Server 2012 has introduced a feature called AlwaysOn Availability Groups which was called out as a single feature which can cover both High Availability (HA) and Disaster Recovery (DR.) Due to this, the feature was earlier known as HADR in initial phases. This feature has multiple sub-features and one of them is “Read-only routing.” The feature is all about sending read-only workload to secondary replica. During initial connection by application to listener, it has to declare its intent (Read-Write or Read-Only.) Based on intent the request would be routed to primary replica (for Read-Write) or secondary replica (Read-Only.) There are some prerequisites for this feature which are:

• Connecting should be made to Listener instead of individual SQL Instance.
• Initial catalog in connection string should be an availability database.
• Read-only routing list (READ_ONLY_ROUTING_LIST) should be defined.
• Routing URL (READ_ONLY_ROUTING_URL) of each instance should be defined.
• ApplicationIntent = ReadOnly should be specified in the connection string.
• Secondary replica should be healthy state (i.e. SYNCHRONIZED or SYNCHORNIZING.)
• Secondary replicas should be set to allow connections (Read-Intent or Readable.)

What’s New in SQL 2016

The limitation of the read-only routing feature was that the connection with ReadOnly intent would be sent to only first healthy secondary replica in the routing list. It would not be a very optimal use of multiple readable secondary replica if only one is hammered by connections and others are sitting idle. SQL Server 2014 allows 8 secondary replica and no one likes to waste their money on hardware that is under-utilized.

In SQL Server 2016, you will be allowed to define a group of secondary replicas in a routing list. The client will take care of sending connections to those grouped replicas in round-robin manner. In our lab environment we have a total 4 SQL standalone instances. MyNode1, MyNode2, MyNode3 and MyNode4. Below is the script to create a routing list as follows:

1. When MyNode1 is primary and the read-intent connection comes, send it to MyNode2 and MyNode3 in round-robin manner. If both are not healthy then send to MyNode4. Notice the parenthesis.

ALTER AVAILABILITY GROUP SQL2016AG
MODIFY REPLICA ON 'MyNode1'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST= (('MyNode2','MyNode3'), 'MyNode4')
)
);

2. When MyNode2 is primary and the read-intent connection comes, send it to MyNode1. If MyNode1 is unhealthy, send to MyNode3 and MyNode4 in round-robin manner.

ALTER AVAILABILITY GROUP SQL2016AG
MODIFY REPLICA ON 'MyNode2'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST= ('MyNode1',('MyNode3','MyNode4'))
)
);

3. When MyNode3 is primary and the read-intent connection comes, send it MyNode4, MyNode1 and MyNode2 in round-robin manner.

ALTER AVAILABILITY GROUP SQL2016AG
MODIFY REPLICA ON 'MyNode3'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST= (('MyNode4','MyNode1','MyNode2')))
)

Here is a quick test. We have used SQLCMD utility to test the connection. We are printing the server name to understand where the connection is redirected.

SQLCMD -S SQLListener -K ReadOnly -d Finance -Q”select @@servername” -W

Here are theparameters explained:

-S <ListenerName>
-K <ReadOnly Intent>
-d <DatabaseName>
-Q <Query>
-W <remove trailing spaces>

1. MyNode1 is primary:

2. MyNode2 is primary:

3. MyNode3 is primary:

Here is the query which tells us the routing:

SELECT ar_Primary.replica_server_name 'Primary',
ar_secondary.replica_server_name 'Route_to',
ror.routing_priority 'Priority'
FROM sys.availability_read_only_routing_lists ror
INNER JOIN sys.availability_replicas ar_Primary
ON ror.replica_id = ar_Primary.replica_id
INNER JOIN sys.availability_replicas ar_secondary
ON ror.read_only_replica_id = ar_secondary.replica_id
INNER JOIN sys.availability_groups ag
ON ag.group_id = ar_Primary.group_id
where ag.name = 'SQL2016AG'
ORDER BY 1,3

This is a great investment made by Microsoft which would help applications leveraging multiple readable secondary replicas by spreading a load across all of them. This would help in utilizing the power of those machines to serve a workload rather than sitting idle. What are your thoughts? We’d love your feedback.