SQL Server 2016 Availability Groups – Load Balancing 101

By | In SQL Server | December 02nd, 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.

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.

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

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:

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.

Eric Russo
Senior Vice President of Database Services
Eric Russo is SVP of Database Services overseeing all of Datavail’s database practices including project and managed services for MS SQL, Oracle, Oracle EBS, MySQL, MongoDB, SharePoint and DB2. He is also the Product Owner for Datavail Delta, a database monitoring tool. He has 21 years’ experience in technology including 16 years in database management. His management success and style has attracted top DBAs from around the world to create one of the most talented and largest SQL Server teams. He has been with Datavail since 2008: previous to that his work experiences include DBA Manager at StrataVia, Senior Web Developer at Manifest Information Systems and SQL Server DBA at Clark County, Nevada.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

2 thoughts on “SQL Server 2016 Availability Groups – Load Balancing 101”