the primary fails and the secondary becomes the primary; the reading load is not desired since it will effect mission-critical loads (that are transferred to the new primary). We can tolerate outage on non-mission-critical readers, so we desire their respective users become disabled and kicked-out until the replica becomes secondary again.
the primary fails and the secondary becomes the primary; the reading load is not desired since it will effect mission-critical loads (that are transferred to the new primary). We can tolerate outage on non-mission-critical readers, so we desire their respective users become disabled and kicked-out until the replica becomes secondary again.
This is not the right way to move read-only traffic when a replica changes role from Secondary to Primary. Disabling user access each time a replica role changes needs a customized solution. I suggest you use Microsoft's built-in solution, which I mentioned below.
Read-only routing is available in SQL Server 2016 (13.x) and later. Before setting up read-only routing, ensure you have the Prerequisites. Here is the documentation for setting up Read-only routing for an availability group.
You must decide what to do with your read-only traffic if none of the secondary replicas are available. You can send that traffic to the primary (you include the primary in the read_only_routing_list) or wait until at least one of the secondaries is available.
Few other considerations before you plan to offload the read-only workload to the secondary replica of an Always On availability group
