Load balancing reads SQL Server 2016 AG

Question

I'm trying to balance read traffic between two SQL Server 2016 Enterprise Edition instances (Primary and Secondary) using availability groups and the read only routing.

Here is my current setup:

• The servers are in AWS in two different AZ
• The servers are in a failover cluster without shared storage
• AG is enabled on both instances
• The databases in an Availability Group have the "Readable Secondary" option set to Yes
• I've setup the AG with the read only routing on both the current primary and secondary instances
• Adding the read only switch in my application connection string allows me to route traffic to the secondary

Is there a way to have the AG Listener load balance read only traffic between the primary and secondary?

Answer 1

On SQL Server 2016+, you can load-balance read-only connections. (Note: this does not work on SQL 2012 or 2014 AGs.)

The full documentation is available here.

Essentially, when configuring your read-only routing list, you would use syntax like this:

ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQL16A' 
    WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL16B', 'SQL16C'), 'SQL16A')));

This will make your first routing option SQL to load-balance between SQL16B & SQL16C. The second routing option is to send read-only connections to SQL16A. If server B is offline, then just that one server is taken out of the rotation--so all read-only connections would continue to go to Server C.

Load balancing is done in a round-robin fashion, so connections will be routed, A,B,A,B,A,B, etc. Load balancing does not consider existing number of connections, existing server load, or anything fancy.

Answer 2

If I understand your question correctly, you wanted to load-balance between primary and secondary replicas for connections with read-only intent.

By default, for connections with ApplicationIntent=ReadOnly parameter are routed to readable secondary replicas.

The way connections are routed to readable secondary replicas are defined at Read-Only Routing List based on their priority.

I have altered my AG Routing list for the above requirement and it is working fine:

USE [master]
GO
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'NODE1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'NODE2',N'NODE1')))
GO

NODE1 is my primary replica and NODE2 is secondary replica. The connection string contains the following connection parameters:

ApplicationIntent=ReadOnly;Initial Catalog = TestDB;

Note: By default, connections are not load-balanced between NODE2 and NODE1, even if you specify load-balancing on read-only routing list. This works, only when, NODE2 is down and it will look for next secondary replica with highest priority.

So, as it is working based on routing list, if first node is not available, connection will be routed to next node. Load balancing between, secondary and primary is not working, even if configured as a load-balancer format in routing list as long as NODE2 is up and running.

To load-balance between NODE2 and NODE3 (both are readable secondary replicas), alter the AG as follows:

USE [master]
GO
ALTER AVAILABILITY GROUP [TestAG]
MODIFY REPLICA ON N'NODE1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'NODE2',N'NODE3'), NODE1))
GO

In this case, it will load-balance between, NODE2 and NODE3 and if both the nodes are not available, it will route to NODE1 (primary replica).

I hope this is helpful to you, check this page for understanding read-only routing.