I recently set up an Availability Group with the intent of using the secondary as a read only replica for reporting.  We have a few AG’s in our environment already but currently none are using this feature.

I’m not going to step through setting up the AG or configuring the readable secondary as there are plenty of good posts out there as well as the official books online documentation.

Once my AG was created I set the ‘Connections in Primary Role’ to ‘Allow read/write connections’ and the ‘Readable Secondary’ to ‘Read-intent only’ as shown below. On a side note it’s important to set these for both instances, if you’re running with 01B as the Primary after a failover by setting both you’ll get the same behaviour, with read only connections being routed to the now secondary, 01A server.

The other part I needed to set up was read-only routing, this enables SQL Server to reroute those read only connections to the appropriate replica.  You can also list the read only replicas by priority if you have multiple available or you can group them to enable load-balancing.

Although this seems to be setup correctly so that connections that specify their application intent of read only will be routed to the secondary node I wanted to prove it. I used the Connect-DbaInstance function from dbatools to connect to the listener name with the -ApplicationIntent property set to ‘ReadOnly’.

1
2
3
4
5
6
7

$connectSplat = @{
  SqlInstance       = 'AGListenerName'
  Database          = 'DatabaseInAG'
  ApplicationIntent = 'ReadOnly'
}
$svr = Connect-DbaInstance @connectSplat
$svr.Query('Select @@ServerName as ServerName')

1
2
3

ServerName
------------
*******01B

You can see it routed correctly to 01B which is currently the secondary node.  If I don’t specify the ApplicationIntent property on the connection it’ll be routed to the primary.

1
2
3
4
5

$connectSplat = @{
  SqlInstance       = 'AGListenerName'
  Database          = 'DatabaseInAG'
}
$svr.Query('Select @@ServerName as ServerName')

1
2
3

ServerName
------------
*******01A

This was a quick and easy way to ensure my read only routing was working as expected, and another great use of dbatools.