Infrastructure at your Service

David Barbarin

SQL Server 2016 : availability groups and load balancing features

Let’s continue with this third post about SQL Server AlwaysOn and availability groups.

Others studies are available here:

 

This time I’ll talk about read-only secondaries and the new load-balancing support that will be introduced by SQL Server 2016.

First of all, SQL Server 2014 improved the read-only secondary availability by solving the issue related to secondary accessibility when the primary is offline. However, the redirection to a readable secondary was still basic because it concerned only the first secondary replica defined in the configured priority list. So, unless using a third-party tool it was not possible to use very efficiently all of the resources available from secondaries. Fortunately, the next SQL Server version will change the game by introducing native load-balancing capabilities.

In order to be able to use this new feature, you must define:

  • The list of possible secondary replicas
  • A read-only route for each concerned replica
  • A routing list that include read-only replicas and load-balancing rules

At this point I admit to expect a GUI for configuring both read-only routes and the routing list rules in a user friendly fashion even if I prefer using T-SQL to be honest. But anyway, let’s try to configure secondary replicas in round-robin fashion as follows:

 

/* enable read-only secondary replicas */
ALTER AVAILABILITY GROUP [2016Grp]
MODIFY REPLICA ON
N’SQL161′
WITH
(
       SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
);
GO
 
ALTER AVAILABILITY GROUP [2016Grp]
MODIFY REPLICA ON
N’SQL161′
WITH
(
       SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://SQL161.dbi-services.test:1433′)
);
GO
 
ALTER AVAILABILITY GROUP [2016Grp]
MODIFY REPLICA ON
N’SQL162′
WITH
(
       SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
);
GO
 
ALTER AVAILABILITY GROUP [2016Grp]
MODIFY REPLICA ON
N’SQL162′
WITH
(
       SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://SQL162.dbi-services.test:1433′)
);
GO
 
ALTER AVAILABILITY GROUP [2016Grp]
MODIFY REPLICA ON
N’SQL163′
WITH
(
       SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
);
GO
 
ALTER AVAILABILITY GROUP [2016Grp]
MODIFY REPLICA ON
N’SQL163′
WITH
(
       SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://SQL163.dbi-services.test:1433′)
);
GO
 
/* configure replicas priority list */
ALTER AVAILABILITY GROUP [2016Grp]
MODIFY REPLICA ON
N’SQL161′
WITH
(
       PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=((‘SQL162′,‘SQL163′)))
);
 
ALTER AVAILABILITY GROUP [2016Grp]
MODIFY REPLICA ON
N’SQL162′
WITH
(
       PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=((‘SQL161′,‘SQL163′)))
);
GO
 
ALTER AVAILABILITY GROUP [2016Grp]
MODIFY REPLICA ON
N’SQL163′
WITH
(
       PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=((‘SQL162′,‘SQL161′)))
);
GO

 

My test lab includes 3 replicas (SQL161, SQL162 and SQL163). The secondaries will be used as read-only replicas with the new load-balancing feature.

Note the double brackets around the replicas list that defines the load-balancing mechanism for the concerned replicas. In my context, I have only two read-only replicas but rules are defined as follows:

  • (replica1, replica2, replica3): no load-balancing capabilities in this case. The first replica will be used, then the second and finally the third.
  • ((replica1, replica2), replica3): replica1 and replica will be used in a round-robin fashion. The replica3 will be used only if both replica1 and replica2 are not available.

Now let’s play with this new infrastructure by using sqlcmd command as follows:

 

blog_52_-_1-_sqlcmd_readonly

 

As reminder, you have to meet some others requirements in order to use correctly the transparent redirection to a secondary replica as using TCP protocol, referencing directly the availability group listener and the concerned database as well and setting the application intent attribute as readonly. So in my case, I reference directly the LST-2016 listener and the killerdb. I use also the –K parameter with READONLY attribute. Finally, I run the query SELECT @@SERVERNAME in order to know which replica I am after login.

I ran this command several times and I can state that the load-balancing feature plays its full role.

 

blog_52_-_2-_sqlcmd_tests

 

However, let’s play now with the following PowerShell script:

 

Clear-Host;
 
$dataSource = “LST-2016″;
$database = “killerdb”;
$connectionString = “Server=tcp:$dataSource;Integrated Security=SSPI;Database=$database;ApplicationIntent=ReadOnly”;
  
$i = 0;
 
while ($i -le 3)
{
 
 
   Write-Host “Test connexion initial server nb : $i$dataSource-NoNewline;
   Write-Host “”;
   Write-Host “”;
 
   Try
   {
       $connection = New-Object System.Data.SqlClient.SqlConnection;
       $connection.ConnectionString = $connectionString;
 
       $connection.Open();
 
       $sqlCommandText=“SELECT ‘Current server : ‘ + @@SERVERNAME as server_name”;
       $sqlCommand = New-Object system.Data.sqlclient.SqlCommand($sqlCommandText,$connection);
       $sqlCommand.ExecuteScalar();
 
       $connection.Close();
 
       $sqlCommand.Dispose();
       $connection.Dispose();
 
   }
   Catch [Exception]
   {
       Write-Host “KO” -ForegroundColor Red;
       Write-Host $_.Exception.Message;
   }
 
   Write-Host “”;
 
   Start-Sleep 3;
 
   $i++;
}

 

The result is not the same. The redirection to a read-only replica works perfectly but there was not load-balancing mechanism in action this time as shown below:

 

blog_52_-_3-_powershell_tests

 

 

What’s going on in the case? In fact and to be honest, I didn’t remember that PowerShell uses connection pooling by default (thanks to Brent Ozar - @BrentO to put me on the right track).

Let’s take a look at the output of an extended event session that includes the following events:

  • sqlserver.login
  • sqlserver.logout
  • sqlserver.read_only_route_complete
  • sqlserver.rpc_completed

 

blog_52_-_4-_xe_sqlcmd

 

You can notice that sqlcmd tool doesn’t use connection pooling (is_cached column = false). In this case for each run, SQL Server will calculate the read-only route.

However for my PowerShell script the story is not the same as shown below:

 

blog_52_-_5-_xe_pw

 

The first connection is not pooled and we can noticed only one read-only route calculation from SQL Server. All of the next connections are pooled and technically they are still alive on the SQL Server instance. This is why the load balancing mechanism is not performed in this case. So this is an important point to keep in mind if you want to plan to benefit to this new feature.

I also had a dream: Having a real load-balancing feature based on resource scheduling algorithm… maybe the next step? :)

See you

 

 

 

8 Comments

  • Sam says:

    “((replica1, replica2), replica3): replica1 and replica will be used in a round-robin fashion. The replica3 will be used only if both replica1 and replica2 are not available.”

    I think you’re missing a “2” in there somewhere…

  • […] SQL Server 2016 : availability groups and load balancing features […]

  • Santosh says:

    Hi ,

    have implemented the same but facing some issue regarding the load balance, unable to trace the round robin.

    its hitting only primary server

    Setting:
    Read-intent only for both primary & secondary
    All Connection
    routing :
    server1 – primary
    server2, server3 – secondary

    instance- readonly URL- Readonly routinglist
    server1 – server2 – server2,server3
    server2 – server3 -server3,server1
    server3 – server1 -server1,server2

    getting below Error when used -K READONLY

    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server..
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: An existing connection was forcibly closed by the remote host.
    .
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection.
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection due to prelogin failure.

    Can you please help me on below issue

    • David Barbarin says:

      Hi Santosh,

      Yes I can.

      Can you put the result of your read-only replica settings from these queries? Replace AgName by your availability group name in the two queries below.

      SELECT
      r.replica_server_name,
      r.read_only_routing_url,
      g.name AS group_name
      FROM
      sys.availability_replicas AS r
      JOIN
      sys.availability_groups AS g ON r.group_id = g.group_id
      WHERE
      g.name = N’AgName’
      ORDER BY
      r.replica_server_name;

      select
      r.replica_server_name AS primary_replica,
      r.read_only_routing_url,
      rl.routing_priority,
      r2.replica_server_name AS read_only_secondary_replica,
      r2.secondary_role_allow_connections_desc,
      g.name AS availability_group
      FROM
      sys.availability_read_only_routing_lists AS rl
      JOIN
      sys.availability_replicas AS r ON rl.replica_id = r.replica_id
      JOIN
      sys.availability_replicas AS r2 ON rl.read_only_replica_id = r2.replica_id
      JOIN
      sys.availability_groups AS g ON g.group_id = r.group_id
      WHERE
      g.name = N’AgName’
      ORDER BY
      primary_replica, availability_group, routing_priority;
      GO

      Regards

      • Santosh says:

        Thanks David
        here are the results for the queries , can you please help me

        1st Query Result

        replica_server_name read_only_routing_url group_name
        ABHAYK-TEST\MGSERVER1 TCP://abhayk-test1.abc.com:5022 JS_AG
        ABHAYK-TEST1\MGSERVER2 TCP://abhayk-test3.abc.com:5022 JS_AG
        ABHAYK-TEST3\MGSERVER3 TCP://Abhayk-Test.abc.com:5022 JS_AG

        2nd Query Result

        primary_replica read_only_routing_url routing_priority read_only_secondary_replica secondary_role_allow_connections_desc availability_group
        ABHAYK-TEST\MGSERVER1 TCP://abhayk-test1.abc.com:5022 1 ABHAYK-TEST1\MGSERVER2 READ_ONLY JS_AG
        ABHAYK-TEST\MGSERVER1 TCP://abhayk-test1.abc.com:5022 2 ABHAYK-TEST3\MGSERVER3 READ_ONLY JS_AG
        ABHAYK-TEST1\MGSERVER2 TCP://abhayk-test3.abc.com:5022 1 ABHAYK-TEST3\MGSERVER3 READ_ONLY JS_AG
        ABHAYK-TEST1\MGSERVER2 TCP://abhayk-test3.abc.com:5022 2 ABHAYK-TEST\MGSERVER1 NO JS_AG
        ABHAYK-TEST3\MGSERVER3 TCP://Abhayk-Test.abc.com:5022 1 ABHAYK-TEST1\MGSERVER2 READ_ONLY JS_AG
        ABHAYK-TEST3\MGSERVER3 TCP://Abhayk-Test.abc.com:5022 2 ABHAYK-TEST\MGSERVER1 NO JS_AG

        • David Barbarin says:

          Assuming 5022 is the endpoint port of your replicas (replication traffic), your configuration is not correct.
          For read-only routes you have to configure the listen port of your SQL Server instances (1433 and so forth …) rather than the replication port. This is why you’re facing this issue.

          Note if you are using named instances with TCP dynamic ports you will have to change it with TCP fixed ports and use them in your read-only configuration.

          Hope it helps

  • Olivier D says:

    This is not really a load balancing solution as it only divert all read towards the secondary node…
    Do you have any solution including write on both node at the same time ?

    • David Barbarin says:

      Hi Olivier,

      I would say this is a load-balancing solution since SQL Server 2016 even if the round-robin algorithm is pretty simple (assuming you have at least 2 secondaries on your architecture).

      Unfortunately, no solution for writing on secondaries (even with SQL Server 2017). You have to rely on other features as SQL Server replication (transactional, merge, P2P …)

Leave a Reply

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader