Select Page

SQL Server 2016 Availability Groups – Load Balancing 101

Author: Eric Russo | 4 min read | 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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Oracle RMAN Backup and Recovery with Restore Points

Oracle restore points are useful for benchmark testing. Find out how you can use Oracle’s Recovery Manager (RMAN) tool to create and use restore points.

Cindy Putnam | May 3, 2019

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

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