This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
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?
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