Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

AlwaysOn Availability Groups: Read-only Routing

  • 29 March 2013
  • Author: Kathi Kellenberger
  • Number of views: 13323
  • 0 Comments

When you set up AlwaysOn Availability Groups (AG) you have the choice for each node, when in secondary mode, to be readable. In fact, you can specify that connections will be accepted by the secondary node only with a special connection string specifying that it intends to be read-only. This is a great feature for offloading any read-only workload onto the secondary.

The figure shows the AG group configuration with the Readable Secondary options. “No” means that connections will be accepted only when the node is in Primary mode. “Yes” means that the node will accept any valid connections, but the database will be effectively read-only. “Read-intent only” means that the node will accept only connections with a special connection string with the Read Intent parameter.

As I talked about here, you set up a virtual name called a listener to redirect connections to the primary node. You can also redirect the read-intent connections by setting up read-only routing.

At this time, there is not a way to set up read-only routing except by writing a script, either in T-SQL or PowerShell. There are three steps: first configure the node for read-intent connections; next set up the read-only routing URL; finally, supply a routing list.

Here is a T-SQL script I could use for the AlwaysOn2 node and an AG group called AG1:

ALTER AVAILABILITY GROUP [AG1]
   MODIFY REPLICA ON
  N'AlwaysOn2' WITH
  (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
--set up read-only routing URL 
ALTER AVAILABILITY GROUP [AG1]
   MODIFY REPLICA ON
  N'AlwaysOn2' WITH
  (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://AlwaysOn2.mydomain.local:1433'));
--set up the routing list
   ALTER AVAILABILITY GROUP [AG1]
  MODIFY REPLICA ON
  N'AlwaysOn2' WITH
  (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('AlwaysOn1','AlwaysOn3','AlwaysOn2')));

Basically, this script configures the node AlwaysOn2 to redirect any read-only connections to the AlwaysOn1 node when AlwaysOn2 is in the primary role. If AlwaysOn1 is not available, the connection will be routed to AlwaysOn3. NOTE: I’m only showing how to configure one node; be sure to configure all the nodes in the group.

Here is the equivalent PowerShell script:

--connect to the server
Set-Location SQLSERVER:\SQL\AlwaysOn2\Default\AvailabilityGroups\AG1\
--create a variable
$primaryReplica = Get-Item "AvailabilityReplicas\AlwaysOn2"
--set Read-Only secondary
Set-SQLAvailabilityReplica -ConnectionModeInSecondaryRole "AllowReadIntentConnectionsOnly" -InputObject $primaryReplica
--set the URL
Set-SQLAvailabilityReplica -ReadOnlyRoutingConnectionURL "TCP://AlwaysOn2.mydomain.local:1433" -InputObject $primaryReplica
--create the list
SET-SQLAvailabilityReplica -ReadOnlyRoutingList "AlwaysOn1","AlwaysOn3","AlwaysOn2" -InputObject $PrimaryReplica
This query shows what I have set up for the AlwaysOn2 node:

SELECT G.name, P.replica_server_name AS PrimServer,
 S.replica_server_name AS SecServer,
 L.routing_priority
FROM sys.availability_read_only_routing_lists L
JOIN sys.availability_replicas P ON L.replica_id = P.replica_id
JOIN sys.availability_replicas S ON L.read_only_replica_id = S.replica_id
JOIN sys.availability_groups G ON G.group_id = P.group_id
WHERE g.name = 'AG1' AND P.replica_server_name = 'AlwaysOn2'
ORDER BY L.routing_priority




The final piece to the puzzle is the connection string. Here is the connection string for AG1, which has a listener configured as AG1_Listener.
Server=tcp:AG1_Listener,1433;Database=AdventureWorks2012;ApplicationIntent=ReadOnly

Setting up read-only routing is not too difficult. Since there is not a way to do this without scripting, it may seem a bit daunting at first, but with examples to follow, you will be well on your way to success.

 

Print
Categories: SQL Server
Tags:
Rate this article:
No rating

Please login or register to post comments.