AlwaysOn Load Balancing DeMystified with SQL Server

By | In SQL Server | April 07th, 2016

Imagine a situation where you have hired 4 team members for a project. All of them are equally capable of handling the work. But due to project manager’s inefficiency, if only one member has to do all the work then it would not be an optimal use of resources. In real life these things happen. But when it comes to software, we are expected to build efficiencies in our very own design.

Let us apply same logic on secondary replicas in AlwaysOn Availability Group configuration. If a Read-Only application is using listener for connectivity, it would always be redirected to first secondary replica in routing list. This was the default behavior until SQL Server 2014, which means there was no concept of “load balancing readable secondary replicas.” Don’t you personally think this is less efficient since we have not used all the available secondaries in this configuration?

AlwaysOn

Since DBA/Architects always want to utilize all readable secondary replicas, they have been using 3rd party solutions like Load Traffic Manager by F5. Based on feedback provided by customers to the Microsoft product team, they have incorporated this as a “feature” in SQL Server 2016. A welcome addition to the present configuration.

AlwaysOn

To demonstrate this, we have configured 4 nodes availability group. All three secondary replicas are set to readable mode. Here are the details:

  • Nodes: SRV1 (Primary), SRV2 (Secondary), SRV3 (Secondary), SRV4 (Secondary)
  • Availability Group Name: MyAG
  • Listener Name: MyListener

Here is the script used to create load-balancing.

If we run above script(s) without creating routing URL for each replica, we would get below error:

Msg 19404, Level 16, State 17, Line 1

An availability replica ‘SRV2’ that is specified in the READ_ONLY_ROUTING_LIST for availability replica ‘SRV1’ does not have a value set for READ_ONLY_ROUTING_URL. Ensure a READ_ONLY_ROUTING_URL is set for each availability replica in the availability group. Specify a valid READ_ONLY_ROUTING_URL for each replica that you want to added to the READ_ONLY_ROUTING_LIST. If you are altering availability replicas of an existing availability group, you can get the names of availability replicas in a given availability group, select replica_server_name from sys.availability_replicas and name from sys.availability_groups. For more information, see SQL Server Books Online.

Once routing is created the connection would round robin based on the parenthesis in the routing group.

Important note

It must be kept in mind that grouping of replicas is necessary, even if there is just one group. There is a difference between below command on round-robin.

The first command is the correct way to create grouping. This would cause a round-robin between all three replicas. On the other hand, the second command would redirect all request to SRV4. Here is the output of the query:

Output when configuration is created by first query. ((‘SRV4′,’SRV1′,’SRV2’))

AlwaysOn

Here is the output when configuration is created by second query. (‘SRV4′,’SRV1′,’SRV2’)

AlwaysOn

To see the internal, we can capture extended events as shown below:

We have created it on SRV1 and then used SQLCMD to connect using below syntax.

SQLCMD -S MyListener -K ReadOnly -d MyDB -Q “select @@servername”

We have made several connections and as we can see below request is being sent to SRV2 and SRV3 in round robin fashion.

AlwaysOn

The port number is useful in some cases where there are multiple instance of SQL running on the same node and we are troubleshooting routing issue.

Our Learning

One of the very common mistakes that DBAs make is that they generate incorrect but valid Read-Only Routing URL. So, if you are running into any routing issues, double-check that the URLs contain correct representations of the host on which SQL Server is running and also importantly the port number. The SQL Server port can be found via SQL Server Configuration Manager or SQL Server ERRORLOG file.

Contact Us
Eric Russo
Senior Vice President of Database Services
Eric Russo is SVP of Database Services overseeing all of Datavail’s database practices including project and managed services for MS SQL, Oracle, Oracle EBS, MySQL, MongoDB, SharePoint and DB2. He is also the Product Owner for Datavail Delta, a database monitoring tool. He has 21 years’ experience in technology including 16 years in database management. His management success and style has attracted top DBAs from around the world to create one of the most talented and largest SQL Server teams. He has been with Datavail since 2008: previous to that his work experiences include DBA Manager at StrataVia, Senior Web Developer at Manifest Information Systems and SQL Server DBA at Clark County, Nevada.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

3 thoughts on “AlwaysOn Load Balancing DeMystified with SQL Server”
  1. 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