Always On availability groups are around for quite some time, but I still see a lot of DBA’s not configuring in the right manner to take advantage of read-scale out and load balancing capability.
So if you have a cluster with two nodes or more and if your read-only workloads can tolerate some data latency, you can configure Always On availability groups to load balance the reads cross all the replicas in your cluster. The trick is to configure correctly SQL Server in order to allow this mechanism to work as expected. In order to do it you need to use T-SQL, SSMS doesn’t allow you to perform the configuration correctly.
Explaining the environment that I’ll be using for this demo. I will be using a three node cluster with one Availability Group.
SQL01 is the Primary it means that is the writing node, SQL02 is read only node synchronous commit act as auto-failover for HA, then another read node SQL03, but this one has data being replicated asynchronously. Also important the AG name AG1 and also the listener with the name LT-AG1 this is where everything in terms of connections goes, and if you want to use the read replicas using the round-robin mechanism, you really need to use the listener for the connections.
Now let’s configure using the Read-Only routing GUID in SSMS v18.9.1.
Open SSMS in object explorer, go to the tree view folder that has Always On High Availability, right mouse click on top of the availability group that you want to configure and SSMS will show in a new window similar to these one…
At initial stage if you don’t have any Rea-Only Routing Configure SSMS will be exactly this last image.
So ideally you need to configure for each node, that is needed because the primary can be any of the nodes in the cluster so always configure for all of them.
Letr’s start to configure in SSMS… But upfront read carefully the information in the SSMS window….
You will see what I mean with my last statement…
Let’s assume that we won’t the following configuration:
Primary Writing node in SQL01 and to have SQL02 and SQL03 as Read-Replicas and we want to load balance the requests in a round-robin fashion, in order to configure this in SSMS you need to select the Server SQL01 in the table that has the Read-only routing summary and in the read -only routing list for that node, select in the available replicas nodes SQL02 and SQL03 and click on the Add button SSMS will move them to the Read-only routing list.
let’s test this configuration… In order to do that I’ve created a simple PowerShell Script to allow me to test this kind of configurations, you just need to replace the $datasource to the listener that you want to test and to replace database name for the database name that is inside of your availability group that you want to test.
#Script created by Ivan Daniel Campos#
#THIS Script IS PROVIDED BY Ivan Campos “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES#
#This script was made to test the Round Robin algorithm present in the listener of AlwaysOn AG's with more than 1 read-replica. #
Clear-Host;
#define a variable to assign the name of the listener#
$dataSource = "LT-AG1";
#define a variable to assign the name of the database #
$database = "Test-DB";
#define a variable to assign all the information of the connection string to be used make sure that pooling is false since
#powershell uses .net to connect and .net uses pooling for connections.#
$connectionString = "Server=tcp:$dataSource;Integrated Security=SSPI;Database=$database;ApplicationIntent=ReadOnly;Pooling=False;";
#initialize the loop#
$i = 0;
#Set the number of iterations that you want to run in the test#
$cnt = 10;
#looping starting#
while ($i -le $cnt)
{
Write-Host “New attempt to run read query on : $i – $dataSource node that executed: ” -NoNewline;
Write-Host '';
Try
{
$connection = New-Object System.Data.SqlClient.SqlConnection;
$connection.ConnectionString = $connectionString;
$connection.Open();
#get the servername of which server you are connecting #
$sqlCommandText="SELECT @@SERVERNAME";
$sqlCommand = New-Object system.Data.sqlclient.SqlCommand($sqlCommandText,$connection);
$sqlCommand.ExecuteScalar();
$connection.Close();
$sqlCommand.Dispose();
$connection.Dispose();
}
Catch [Exception]
{
Write-Host "Failed" -ForegroundColor Red;
Write-Host $_.Exception.Message;
}
Write-Host "";
Start-Sleep 1;
$i++;
}
The result was this one:
Clearly we can see only SQL02 replying why is that? In short way because we didn’t configure a set of nodes, we just defined the replicas that are in the read-only routing list sorted by priority in this case the first is SQL02. So now lets define the set of replicas and for that we can’t use SSMS GUID we need to do it using T-SQL:
USE [master]
GO
ALTER AVAILABILITY GROUP [LT-AG1]
MODIFY REPLICA ON N'SQL01' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = ((N'SQL02',N'SQL03'))))
GO
Notice in the script that we are using () to define the set of replicas, now let’s test our configuration
Now yes we are using the round-robin mechanism correctly because every single connection is being redirect for each time to different nodes, another way to see if it is correct is using SQLCMD, ex:
-sqlcmd -S "LT-AG1" -d WideWorldImporters-E -q "SELECT @@SERVERNAME;" -Kreadonly
You can also see this configuration reflected in SSMS, with brackets
Ok that’s it, I hope this article helps you in the future