Introducing the New SQL Server 2016 Availability Groups Load Balancing of Read-Only Replicas

About four years ago, I did my very first webcast on the Availability Groups feature in SQL Server 2012. The premise of the presentation was how we can provide high availability features to existing SQL Server workloads without making changes to the underlying storage. For my demo, I used an existing database mirroring configuration for high availability with log shipping for disaster recovery as my Availability Group replicas. One of the features that I highlighted was read-only routing for readable secondary replicas.

One of the most common question that I get from customers who have already implemented Availability Groups with readable secondary replicas is the ability to load balance the incoming read-only workload across multiple replicas. When I get asked this question, I assume the following:

  • They have an Enterprise Edition license for all of the readable secondary replicas, including the primary, of course
  • They will update their applications to use the latest SQL Server Native Client
  • They will include the Application Intent=ReadOnly property in their application connection string

If you take a look at the SQL Server Books Online documentation for implementing read-only routing, you can see the note below, emphasis mine.

ReadOnlyRoutingSQL2012

Indeed, as of SQL Server 2014, load balancing the read-only workloads across all available readable secondary replicas is not supported. Customers would end up implementing their own custom solution like having a hardware load balancer like the F5 Big IP LTM in front of the Availability Group to handle the read-only workload. They would work really well especially if your goal is to just balance the read-only workloads. The only challenge here is that this layer is managed by a different team – the network engineering team. It means that HA/DR processes need to be modified to include the complexities of this layer. It also means that the communication also needs to be well defined for escalation.

It’s why customers have asked for a better option: why not have this feature available in the product?

An Out-of-the-Box Feature

Microsoft listened to their customers. SQL Server 2016 now has this feature available. You can try it out for yourself to see how it works.

ReadOnlyRoutingSQL2016

But let me explain how it worked in SQL Server 2012/2014 and how it works in the upcoming version of SQL Server.

What You Need

  • Availability Group listener name. I call this a virtual server name because it encapsulates application connectivity to the database. You can move your databases to different SQL Server instances with different hostnames without changing your application connection string. In order for read-only routing to work, the Availability Group must have a listener name. Some customers do not use a listener name because of their specific network configuration. But in order to take advantage of read-only routing, you need a listener name.
  • Readable Secondary Replicas. By default, Availability Group secondary replicas are in constant recovery mode. This is because the REDO process is constantly replaying transaction log records. In order to allow read-only queries to Availability Group secondary databases, you need to configure them with read-only access.
  • Read-only routing URL. The read-only routing URL is an endpoint that a read-only client application connects to. The key to this is having the Application Intent=ReadOnly property in the connection string. It has to be configured on all of the replicas that you want to assign as a readable secondary, as per the last item in this list. The read-only routing URL only takes effect when the current replica is running as a secondary replica. You can think of it as an ON/OFF switch – it is turned OFF when the current replica is acting as primary replica.
  • Read-only routing list. As the name implies, this is a list of readable secondary replicas where you want your read-only client applications to connects to, again, as per the last item in this list. Unlike the read-only routing URL, the read-only routing list only takes effect when the current replica is acting as a primary replica – a complete opposite of the read-only routing URL.
  • Licenses. I cannot overemphasize this. The moment you run any workload on a standby SQL Server instance, it will require a license. Same is true for an Availability Group readable secondary replica.

How It Works in SQL Server 2012/2014

In SQL Server 2012/2014, when a client application connects to the Availability Group via the listener name, it gets evaluated for the Application Intent=ReadOnly property in the connection string. If the connection string attribute exists, it checks the read-only routing list. As per the SQL Server Books Online documentation,

Read-intent connection requests are routed to the first available readable secondary on the read-only routing list of the current primary replica

This means that the algorithm will read the routing list, and stops at the very first one that responds. For example, say you have SERVER1, SERVER2 and SERVER3 in the read-only routing list. If SERVER1 immediately responds to the request, then all the subsequent read-only client applications will only be redirected to SERVER1. Depending on the read-only workload, this may introduce a ton of activities on SERVER1 while SERVER2 and SERVER3 are sitting there idle, doing nothing but receiving transaction log records from the primary replica and running REDO. The only time that SERVER2 would be used for read-only workloads is when SERVER1 becomes unavailable.

How It Works In SQL Server 2016

The behavior is similar in SQL Server 2016. The big difference – availability of load balancing across all readable secondary replicas. As per the SQL Server Books Online documentation,

Read-intent connection requests are routed to the first available entry on the read-only routing list of the current primary replica. However, load-balancing across read-only replicas is supported.

This means that instead of just stopping at the first readable secondary replica in the read-only routing list, it will go thru all of the items in the list and redirect read-only client applications on all of them. Woohoo!

The Magic of the Parenthesis

In mathematical expressions, parentheses are used to denote modifications to normal order of operations (precedence rules.) By default, when you configure an Availability Group read-only routing list using the same way as you did before, the behavior will be like that of SQL Server 2012/2014. For example, using the T-SQL code below to define a read-only routing list to an existing Availability Group with three replicas, all read-only client applications will be routed to SERVER2 but not on SERVER3 unless SERVER2 becomes unavailable.

ALTER AVAILABILITY GROUP SQL2016AG
MODIFY REPLICA ON
N'SERVER1' WITH
(PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST=('SERVER2','SERVER3')
)
);

This is the exact same syntax that you would use with SQL Server 2012/2014. What will make it balance the read-only workload using a round-robin algorithm is the use of an additional parenthesis inside the read-only routing list. For example, using the T-SQL code below to define a read-only routing list to an existing Availability Group with three replicas, all read-only client applications will be routed to SERVER2 first, then, to SERVER3. I’ve highlighted the additional parenthesis for emphasis

ALTER AVAILABILITY GROUP SQL2016AG
MODIFY REPLICA ON
N'SERVER1' WITH
(PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST=(('SERVER2','SERVER3'))
)
);

SQL2016AG-ReadOnlyRoutingList

SQL Server Books Online calls this the nested parenthesis. By using the nested parenthesis, you can configure load balancing of read-only workloads across all or just a subset of your readable secondary replicas. At this time, only one level of nested parenthesis is supported for load balancing of read-only workloads.

Only New Connections Get Re-routed

When an Availability Group failover happens, the Windows Server failover Cluster, takes the resource group offline. This includes taking the Availability Group listener name offline. Depending on how your client applications are written, a connection to a readable secondary replica can be retained during a failover. Of course, this assumes that the current readable secondary replica that the client application is connected to does not become the new primary replica. If it does, the client application will get disconnected and re-routed to the routing list defined on the current primary. This is to make sure that interruptions to read-only workloads are avoided whenever necessary.

For example, say SERVER1 is the current primary replica while SERVER2 and SERVER3 are configured as readable secondary replicas. If the Availability group is moved from SERVER1 to SERVER2, read-only workloads on SERVER2 will be redirected to SERVER1. However, read-only workloads on SERVER3 will remain since it is still acting as a secondary replica. New connections to the Availability Group will be evaluated based on the current primary replica; read-only workloads will be routed accordingly.

Test It Out

I have been working with SQL Server 2016 for a while now and tested out these features. I have been using my good-old Windows client application (it requires .NET Framework 4.0) from my very first Always On Availability Group presentation for testing this behavior. The one thing that I noticed was that sometimes the client application does not automatically get redirected to the other read-only replicas for load balancing. For example, if I move the Availability Group from SERVER3 to SERVER1 as primary, I expect the read-only client applications to be automatically redirected to SERVER2 and SERVER3. Sometimes, they are all pegged to SERVER2. I think it’s because when SERVER1 becomes the new primary, my read-only routing list is defined with SERVER2 and SERVER3, respectively. Since SERVER2 is still serving read-only requests, nothing changes. But the change from primary to secondary role on SERVER3 causes it to get disconnected. When it reconnects back, it just follows the sequence of secondary replicas defined routing list – connects first to SERVER2. This creates the impression that read-only client connections are just getting routed to SERVER2 when, in fact, it works as expected.

I’ve provided this feedback to the SQL Server product team responsible for Availability Groups. They have been very helpful in answering some of my questions. All I can say is that they have a really great team that understands what their customers’ needs are and prioritize on what matters.

I’ll write another blog post on the results of my testing the new database-level failure detection in SQL Server 2016 Availability Groups.

Feeling helpless and confused when dealing with Windows Server Failover Clustering  (WSFC) for your SQL Server databases?

You’re not alone. I’ve heard the same thing from thousands of SQL Server administrators throughout my entire career. These are just a few of them.

“How do I properly size the server, storage, network and all the AD settings which we do not have any control over?”

“I don’t quite understand how the Windows portion of the cluster operates and interacts with what SQL controls.”

“I’m unfamiliar with multi-site clustering.”

“Our servers are setup and configured by our parent company, so we don’t really get much experience with setting up Failover Clusters.“

If you feel the same way, then, this course is for you. It’s a simple and easy-to-understand way for you to learn and master how Windows Server Failover Clusters can keep your SQL Server databases highly available. Be confident in designing, building and managing SQL Server databases running on Windows Server Failover Clusters.

But don’t take my word for it. Here’s what my students have to say about the course.

“The techniques presented were very valuable, and used them the following week when I was paged on an issue.”

“Thanks again for giving me confidence and teaching all this stuff about failover clusters.”

“I’m so gladdddddd that I took this course!!”

“Now I got better knowledge to setup the Windows FC ENVIRONMENT (DC) for SQL Server FCI and AlwaysON.”

NOTE: Registration for my online course Windows Server Failover Clustering (WSFC) for the Smart SQL Server DBA will re-open in January 2018. But be sure you do not miss out. This will be the last time that the course will be offered. After this, you will no longer be able to register for the course.

 

Get notified about the next batch of enrollment so you don’t miss out.

* indicates required




Posted on
Category: SQL Server
...

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

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

8 thoughts on “Introducing the New SQL Server 2016 Availability Groups Load Balancing of Read-Only Replicas”

  3. For load balancing
    Server A — RORL( Server B, Server C)
    Scenario 1)Server A Failover to Server B
    Connection C remain unaffected
    All previous connection to A will be divided between B&C

    Here my question is Server C still available as listener not available.So connection will be disconnected.

    Connection will still be divided among B and C so C will be overloaded

    Reply

    • Since Server C remained online, client connections to Server C will not be affected. However, previous connections to Server A will not be divided between Server B and Server C. Read/write connections will get redirected to Server B since this is now the new primary replica. New read-only connections will be redirected to Server A and Server C, depending on the order of definition in the READ_ONLY_ROUTING_LIST.

      In SQL Server 2012, if the primary replica becomes unavailable, all read-only clients will get disconnected. In SQL Server 2014 and higher, read-only clients will continue to work even when the primary replica becomes unavailable. The listener name is only used to connect to the primary replica and retrieve the list of secondary replicas in the READ_ONLY_ROUTING_LIST. But once the read-only connections already have the instance name of the secondary replicas configured as readable secondary, they no longer need the listener name. Server C will only become overloaded if the READ_ONLY_ROUTING_LIST is not properly configured and load balancing of readable secondary replicas is not properly configured. There are other factors like read-only routing URL, TCP endpoints, etc. that can affect the load balancing of the read-only workload.

      I cover this in my training class SQL Server Always On Availability Group: The Senior DBA’s Field Guide.

      Reply

  4. Is there a way to monitor, visualize, or report on which queries are being processed on which server with Read-Only replicas?

    Reply