patternsqlMinor
Is an automatic failover to the primary replica of a readonly replica in an availability group possible?
Viewed 0 times
theprimarygroupreplicapossiblefailoveravailabilityautomaticreadonly
Problem
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?
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?
Solution
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:
There are some requirements for connection strings as well,if you want to use read only routing:
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.
Context
StackExchange Database Administrators Q#151398, answer score: 4
Revisions (0)
No revisions yet.