Select Page

SQL Server 2016 Availability Groups – Load Balancing 101

Eric Russo | | December 2, 2015

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:

savg1

2. MyNode2 is primary:

savg2

3. MyNode3 is primary:

savg3

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

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS