Overview

In a previous post titled “Read Only Routing with SQL Server 2012 Always On Database Availability Groups” I walked through the details of how SQL Server 2012 can scale read-only connections from an application that only intends to read from the database to a specific node in the cluster.    

In the previous post I showed a script that I used to configure the read-only routing list in order of priority.  In this post I will go one step further and show a way to load balance read intent connections across the secondary replicas.   This involves a script that will rotate the priority of the Read Only Routing list to provide a load balancing affect.

Overview of SQL Server Always On Infrastructure

To facilitate the load balancing example I am using the same SQL Server 2012 Always On infrastructure as the last example.  Below is an overview:

Always On Database Availability Group Diagram:

 

Diagram of Server Manager:

My Windows 2008 R2 server names are (3 node cluster):

I have loaded a SQL 2012 named instance on each server with the following names:

Availability Group Listener (This is required to be setup for read-only routing):
SQL1

NOTE: The Availability Group Listener is much like a virtual network name in traditional SQL clustering as it is a pointer to the SQL Instance that currently hosts the database availability group.  

Read-Only Routing list

The Read-Only routing URL’s and Read-Only routing list is configured using the script below:

--Set ROR URL’s

ALTER AVAILABILITY GROUP [AGAdvWorks1]

Modify Replica on

N'SP-DENALI3\SQL1' with

(Secondary_Role (READ_ONLY_ROUTING_URL = N'tcp://SP-Denali3.contoso.com:56537'))

ALTER AVAILABILITY GROUP [AGAdvWorks1]

Modify Replica on

N'SP-DENALI1\SQL1'Modify Replica on

N'SP-DENALI3\SQL1' with

(Secondary_Role (READ_ONLY_ROUTING_URL = N'tcp://SP-Denali3.contoso.com:56537'))

ALTER AVAILABILITY GROUP [AGAdvWorks1]

Modify Replica on

N'SP-DENALI1an> with

(Secondary_Role (READ_ONLY_ROUTING_URL = N'tcp://SP-Denali1.contoso.com:58950'))

ALTER AVAILABILITY GROUP [AGAdvWorks1]

Modify Replica on

N'SP-DENALI4\SQL1' with

(Secondary_Role (READ_ONLY_ROUTING_URL = N'tcp://SP-Denali4.contoso.com:64445')) 

--Read-Only Routing list

ALTER AVAILABILITY GROUP [AGAdvWorks1]

Modify Replica on

N'SP-DENALI1\SQL1' with

(Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali4\SQL1','SP-Denali3\SQL1'))) 

ALTER AVAILABILITY GROUP [AGAdvWorks1]

Modify Replica on

N'SP-DENALI3\SQL1' with

(Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali4\SQL1','SP-Denali1\SQL1'))) 

ALTER AVAILABILITY GROUP [AGAdvWorks1]

Modify Replica on

N'SP-DENALI4\SQL1' with

(Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali1\SQL1', 'SP-Denali3\SQL1'))) 

NOTE: For a deeper explanation and a VERY handy script that will generate the ROR URL for you please see Matt Neerincx’s blog post http://blogs.msdn.com/b/mattn/archive/2012/04/25/calculating-read-only-routing-url-for-alwayson.aspx

With the above Read-Only Routing URL list when SP-DENALI1\SQL1 is online in the PRIMARY role and an application connects using ApplicationIntent=ReadOnly the connection will be routed to the third node SP-DENALI4\SQL1.  It is possible though to rewrite the Read-Only Routing URL list periodically maybe every 15 seconds specifying different readable secondary’s each time.  This would in affect load balancing the read only connections across the available secondary’s.

Load Balancing using the Read-Only Routing list

It is possible to rewrite the Read-Only Routing list periodically and in effect create load balancing of read intent connections.  In my configuration I have two secondary replicas so each node will have a SQL Agent job that will rewrite the Read-Only Routing list accordingly.

To create a load balancing affect I have created a single script that could be scheduled via SQL Agent job and would run continually.  Every 15 seconds the script changes the Read-Only Routing list so that read intent connections are spread across the available nodes. 

The agent job should be created and running continuously on each node.  The code within the script checks to see if the instance is in the PRIMARY role and also checks to see if more than one secondary is available only executes if the two conditions are true.  The code will only run from the instance that is in the PRIMARY role.

Below is the code that will produce the load balancing effect:

While 1=1

           Begin 

            If (

                  SELECT ARS.role_desc

                  FROM SYS.availability_replicas AR

                  join sys.dm_hadr_availability_replica_states ARS on AR.replica_id = ARS.replica_id

                  where AR.replica_server_name = (select @@SERVERNAME)

                  ) = 'PRIMARY' and (select count(*) from sys.availability_read_only_routing_lists)        > 1            
           Begin  

            ALTER AVAILABILITY GROUP [AG AdventureWorks]

            Modify Replica on

            N'SP-DENALI1\SQL1' with

  ;     
           Begin  

            ALTER AVAILABILITY GROUP [AG AdventureWorks]

            (Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali3\SQL1','SP-Denali4\SQL1'))) 

            ALTER AVAILABILITY GROUP [AG AdventureWorks]

            Modify Replica on

            N'SP-DENALI3\SQL1' with

            (Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali1\SQL1','SP-Denali4\SQL1'))) 

            ALTER AVAILABILITY GROUP [AG AdventureWorks]

            Modify Replica on

            N'SP-DENALI4\SQL1' with

            (Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali3\SQL1', 'SP-Denali1\SQL1'))) 

            --print 'changing ROR URL in 15 seconds...'

            WAITFOR DELAY '00:00:15'

            --print 'Changing ROR URL' 

            --Run every 15 seconds

            ALTER AVAILABILITY GROUP [AG AdventureWorks]

            Modify Replica on

            N'SP-DENALI1\SQL1' with

            (Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali4\SQL1','SP-Denali3\SQL1'))) 

            ALTER AVAILABILITY GROUP [AG AdventureWorks]

            Modify Replica on

            N'SP-DENALI3\SQL1' with

            (Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali4\SQL1','SP-Denali1\SQL1'))) 

            ALTER AVAILABILITY GROUP [AG AdventureWorks]

            Modify Replica on

            N'SP-DENALI4\SQL1' with

            (Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali1\SQL1', 'SP-Denali3\SQL1')))         

            end

WAITFOR DELAY '00:00:15'

End

To complete the procedure either run the code from above in a new query window in SSMS or use the code from above and create a SQL agent job and then start the job.  This job will need to be running continuously.

Verify ROR list is rotating correctly by querying DMV’s:

The below query shows the Read-Only Routing List for each instance:

select distinct(AR.replica_server_name), ROR_List.routing_priority, AR2.replica_server_name

from sys.availability_read_only_routing_lists ROR_List

join sys.availability_replicas AR on ROR_List.replica_id = AR.replica_id

join sys.availability_replicas AR2 on AR2.replica_id = ROR_List.read_only_replica_id



After 15 seconds…  As the script continues to run you can see that the ROR list is changing (Rotating to the right):

 

Verify Load Balancing using a reporting application

Using an SSRS report with a data source like seen below you can verify that connections are in fact being routed between the two different nodes:



 

Running the report:



Close out and reopen the report in SQL Server Data Tools every 15 seconds to verify that the server name is changing.  You can chose to run a SQL profiler trace on both secondary’s to see the load balancing as it is happening or you can use SQL Server Data Tools.  I would recommend closing out of SQL Server Data Tools and also deleting the data cache files *.rdl.data.

Summary

Extending the Read-Only Routing feature in SQL Server 2012 with a script similar to the one used in this blog makes it possible to provide load balancing of read intent connections.  Load balancing using this technique provides a way to get even more use from server hardware that host secondary’s databases and provide reporting applications with better performance and throughput.