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:
$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.