Load balancing reads SQL Server 2016 AG

April 19, 2017 Andy DBA Stack Exchange 0

clip art of a double-quote character

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?

asked 2017-04-19 by Aaron

Answer

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.

answered 2017-04-19 by Andy Mallon