SQL 2016 Load Balanced Readable Secondary Replicas

  • Article

Continuing the theme of new features in SQL 2016, today I’ll introduce read workload load balancing with AlwaysOn Availability Groups.

What is a readable secondary?

A secondary replica can allow read-only access to all its secondary databases in the form of user connections or application connections.  Even though we class this as “read only” the databases that make up the availability group are not explicitly set as a read-only database.  These databases are “dynamic” meaning that changes made on the primary are written to these secondary databases.

One of the biggest benefits in SQL2012 and SQL2014 is that you can route read-only workloads, such as reporting, or SSIS data extraction, to these readable secondary databases through an availability group listener – and this of course eases the load on your primary and also lets you utilize more of your hardware infrastructure!

Configuring Read-Only Routing in SQL 2012, SQL 2014 and SQL 2016

Read only routing cannot yet be configured using the GUI so the available options are TSQL or PowerShell. 

Configuration is simply done by adding the following TSQL statements during the initial ADD REPLICA or MODIFY REPLICA command:

SECONDARY_ROLE ( ALLOW_CONNECTIONS = READ_ONLY) – Set replica to allow read only connections

SECONDARY_ROLE ( READ_ONLY_ROUTING_URL = 'TCP://system-address:port' ) – url to the server – i.e. TCP://SQL01.TestLab.com:1433

PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST = ( 'server' [ ,...n ] ) ) – List of readable secondary replicas

The final part of configuring read-only routing is the connection string used to connect to the instance which is initiating the read only workload must contain the “ApplicationIntent=ReadOnly” attibute.

New in SQL 2016

Configuring read-only routing in SQL 2016 is pretty much the same as before, except now we can group our servers using parenthesis in the READ_ONLY_ROUTING_LIST to create a round robin, “load balanced” effect.

For example:

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), ('Server3', 'Server4', 'Server5'), 'Server6')

Simple round robin readable secondary example

In my simple set up I have 4 HyperV VMs running Windows 2016 Technical Preview 4 and SQL Server 2016 CTP3.2. 1

I have a very simple application which will connect to the availability group via the availability group listener using a read intent connection.  The connection string is built inside the app, of course the connection string would typically be in some sort of config file.

The connection string looks like so:

ConnStr = "Server = tcp : " & Listener & ";Database=" & Database & ";Integrated Security=SSPI;ApplicationIntent=ReadOnly"

With my above configuration I want my read only workload to always go on my second data center and only come across to the primary data center if the opposing DC is down.  Here are my business requirements:

  • Primary replica will be SQL01
  • Round robin the read only workload across SQL03 and SQL04
  • If DC2 is down then send read only workload to SQL02
  • If SQL02 becomes the new primary replica, then send read only workload to SQL01 (if DC2 is down)
  • If a failover occurs to DC2 and SQL03 becomes the new primary replica then the read only workload should go to SQL01 and SQL02
  • If DC1 is down then send read only workload to SQL04
  • If SQL04 becomes the new primary replica then send read only workload to SQL03 (if DC1 is down)

Here is the TSQL for this configuration (this assumes the AG has already been created):

-- Set SQL01 to allow read only connections 
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL01'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Set SQL01 readonly routing url
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL01' 
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL01.loundy.lab:1433'));

-- Set SQL02 to allow read only connections
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL02'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Set SQL02 readonly routing url
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL02' 
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL02.loundy.lab:1433'));

-- Set SQL03 to allow read only connections
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL03'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Set SQL03 readonly routing url
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL03'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL03.loundy.lab:1433'));

-- Set SQL04 to allow read only connections
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL04' 
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Set SQL04 readonly routing url
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL04' 
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL04.loundy.lab:1433'));

-- If SQL01 is the primary replica - where should the read workload go
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL01' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL03','SQL04'),'SQL02')));

-- If SQL02 is the primary replica - where should the read workload go
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL02'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL03','SQL04'),'SQL01')));

-- If SQL03 is the primary replica - where should the read workload go
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL03'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL01','SQL02'),'SQL04')));

-- If SQL03 is the primary replica - where should the read workload go
ALTER AVAILABILITY GROUP [AW_AG]
MODIFY REPLICA ON N'SQL04'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL01','SQL02'),'SQL03')));

It’s worth noting at this point that you cannot create very complex routing lists by nesting groups inside groups.

So now let's run the simple application to see the effect of this configuration.  The application will simply issue a SELECT @@SERVERNAME command and log it to a .txt file.

It’s probably a good point here to note the following requirements for read-only workloads:

  • Connection must be via the listener
  • Connection string must contain the ApplicationIntent=ReadOnly property
  • Connection string must contain the database in the InitialCatalog property
  • A Routing list must be present
  • Client must be supporting latest providers

On first run, as we expect, the output is SQL03: run1

Second run, SQL04: run2

Third run, SQL03: run3

Fourth run, SQL04: run4

And if my DC1 fails, SQL02: run5

As you can see the round robin effect is simply go to one server then the other, as dictated by the grouping scripts above, and if my DC fails, move over to the primary DCs secondary replica and never issue a read only workload on the primary replica.

Troubleshooting

If you are having trouble issuing a read only workload on a secondary check that the routing URL has been configured, also check that you can connect directly to the readable secondary and issue a read-only query (using sqlcmd).  The following query will query your AG and provide the connection string to use along with the routing priority.  If there is no routing URL create one and try the read only connection again:

SELECT ar.replica_server_name, arorl.routing_priority, ar2.replica_server_name, ar2.read_only_routing_url, REPLACE(REPLACE(ar2.read_only_routing_url,'TCP://',''),':',',') AS ConnectionString
FROM sys.availability_read_only_routing_lists arorl
JOIN sys.availability_replicas ar
ON arorl.replica_id = ar.replica_id
JOIN sys.availability_replicas ar2
ON arorl.read_only_replica_id = ar2.replica_id ORDER BY ar.replica_server_name, arorl.routing_priority

There are a few extended events that can also be useful in troubleshooting the state of read only routing.  At the time of this writing, these extended events can only be found under the "Debug" channel.

sqlserver.hadr_evaluate_readonly_routing_info This event will be triggered when the AG comes online, or there are property changes to the replica for routing and the list is accessed for routing.  The field is_routing_replica_not_found values will be true/false hadr_evaluate_readonly_routing_info

sqlserver.read_only_route_complete
This will be triggered when we are able to send a replica URL to the client.  However, this does not mean the connection will be successful.  If the URL is no good, or there are connectivity issues to the replica, the connection will fail, but this event will show complete. read_only_route_complete

sqlserver.read_only_route_fail  ​
The read-only routing operation failed. This is sent when the primary replica has a READ_ONLY_ROUTING_LIST configured and none of the replicas in the list are available for read-only routing. read_only_route_fail

Note:  I've added some additional properties for this XE output to make the output a little more useful

Learn More

SQL 2012 readable secondary https://msdn.microsoft.com/en-gb/library/ff878253(v=sql.110).aspx

SQL 2012 configure read-only routing https://msdn.microsoft.com/en-gb/library/hh710054(v=sql.110).aspx

SQL 2016 readable secondary https://technet.microsoft.com/en-us/library/ff878253(v=sql.130).aspx

SQL 2016 configure read-only routing https://technet.microsoft.com/en-us/library/hh710054.aspx

Connecting to SQL using Application Intent Read-Only MS Blog - https://blogs.msdn.com/b/alwaysonpro/archive/2013/08/02/connect-to-sql-server-using-application-intent-read-only.aspx