Contents
How to enable read only routing in SQL Server?
The first thing that we need to do is configure the secondary replica to allow read-only workload. This can be done by modifying the Readable Secondary option in the Availability Group Properties window. There are three different options under the Readable Secondary drop-down menu. No. This is the default value.
Is there way to update read only routing list?
As a workaround, the Read-Only routing list can be updated periodically, so that you will make sure that all replicas will be used to serve the read-only workload. SQL Server 2016 removes this limitation by introducing the native load balancing mechanism, as we will see later.
How to configure read only routing for an availability group?
From the Availability Group Properties window, change the Readable Secondary properties of each secondary replica with YES value to allow all connections for read-only access or Read-Intent Only to allow only the read-intent connections.
How to check synchronization status in SQL Server?
The synchronization status can be checked from the Databases node of the SQL Server Management Studio as follows: Now the Read-Only Routing is fully configured and the secondary replicas are ready to receive the read-only workload.
How to configure read only routing for an availability?
Each readable secondary replica can be assigned a read-only routing URL that will be used for routing read-intent connection requests to a specific readable secondary replica. In this way, the read-only routing URL is defined on a replica-by-replica basis.
What does read only mean in SQL Server?
Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role).
Where to find AlwaysOn read only routing lists?
The Microsoft SQL Server product team has provided a DMV for this information, sys.availability_read_only_routing_lists , but by itself it is rather cryptic: Enhancing the query a little bit, will yield much more helpful information: