I want to set up an availability group with two nodes. One node will be the primary replica and the second one will be set up as a read-only replica.

In my application I do all active stuff with a read/write-connection and the reporting stuff with a read-only connection. The read-only connection will connect to the read-only replica.

But what happens if the node with the read-only replica goes offline? As far as I understand the documentation, a read-only connection can't be established any more if there is no read-only replica available in the group.

Is there a mechanism I can use to fallback with the read-only connection to the primary replica? Or do I have to build such a fallback manually in my application?


This is covered in the documentation: Configure Read-Only Routing for an Availability Group (SQL Server), but for this approach to work, you will need to configure your read only routing URL on both replicas.

Connections with the "ReadOnly" property set in the connection string will be redirected to the secondary replica. If this secondary replica is not readable (as determined by the ConnectionModeInSecondaryRole setting), the connection will be directed back to the primary replica.

This is a PowerShell example from MSDN on how to add read only routing URLs to both the replicas available:

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"  

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica 

There are some requirements for connection strings as well,if you want to use read only routing:

  • Use the TCP protocol.
  • Set the application intent attribute/property to readonly.
  • Reference the listener of an availability group that is configured to support read-only routing.
  • Reference a database in that availability group.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.