Deny access to Sql Server replica when it becomes primary

Question

TL;DR

How to allow read-only access to some users on one replica (in an Sql Server availability group), only when the replica is secondary, and deny it when it becomes primary?

Explanation

I'm thinking of an availability group setup, where there will be two replicas, one of them readable when it is secondary. The non-mission-critical reader applications (like BI and such) connect to the secondary; they cause a considerable amount of load on that replica; there is plenty of unused resources (since it is secondary replica), so no worries.

Now, 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.

How would it be possible?

Answer 1

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