Load Balancing with Availability Groups


With SQL Server 2016 comes AlwaysOn Availability Groups. This was called out as a single feature which can cover both High Availability (HA) and Disaster Recovery (DR.) Due to this, the feature was known as "HADR" in initial phases. AlwaysOn Availability Groups have 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.)

Here are some prerequisites for this feature:

  • Connecting should be made to Listener instead of individual SQL Instance.
  • Initial catalog in connection string should be an available 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 the 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. SQL Server 2016 allows you to define a group of secondary replicas in a routing list. The client will take care of sending connections to those grouped replicas in a 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:

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 parentheses.

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 a 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 to MyNode4, MyNode1 and MyNode2 in a 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
Primary Route_to Primary
MYNODE1 MYNODE2 1
MYNODE1 MYNODE3 1
MYNODE1 MYNODE4 2
MYNODE2 MYNODE1 1
MYNODE2 MYNODE3 2
MYNODE2 MYNODE4 2
MYNODE3 MYNODE4 1
MYNODE3 MYNODE1 1
MYNODE3 MYNODE2 1

This is a great investment made by Microsoft which will help applications leveraging multiple readable secondary replicas by spreading a load across all of them. This will help in utilizing the power of those machines to serve a workload rather than sitting idle.