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?
Best 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:
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.