March 24, 2021 at 10:16 am
We currently use Always On Availability Groups on SQL Standard.
One of use customers has expressed interest in real time reporting and I'm exploring the option of upgrading their instance to SQL Enterprise to enable read only access to the secondary replica.
They intend to connect with PowerBI to the database. I've read that we can enable Read Only Routing to ensure that any connection coming from a connection string that passes READONLY as the property value for the "Application Intent" property will automatically connect to whichever replica is currently the secondary. That's great, makes sense.
Now, where I'm confused is how to control access.
Assuming I have a dedicated AD User Account / Login (on all replicas with same SID) for this reporting purpose which is mapped to a DB User with object level permissions on the tables they need for their reporting purposes, what happens if the user ignores this setting or just enters the normal Listener name, or the Primary replica host name? What stops them connecting to the Primary or live data in that scenario?
Do I need to automatically enable/disable Logins on the event of a failover on each replica/instance as I currently do with my SQL Jobs to ensure they only run on the Primary, or is there a simple, built in way to ensure that a specific user can only ever connect to the Secondary Replica?
Thanks
March 24, 2021 at 6:00 pm
No, there is not a simple way unfortunately. Unless someone knows something I don't!
Create the read-only logins on both servers, disable them on the primary.
I set up a script to execute when the event "Alert - Error 1480: Database mirroring session or availability group failed over" is triggered that enables/disables the read only logins, and also a SQL job that runs hourly that executes the same code.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 24, 2021 at 6:16 pm
I would not utilize read-intent for this scenario - that really should only be used for offloading application reads and is not intended to offload reporting users access.
Instead - upgrade to Enterprise Edition and create a 3-node cluster with 2 secondary replicas. The first replica will be used for HA - with an AG containing all databases, a listener and automatic failover (synchronous mode) and not set to read-intent or read-only. The second replica would then be setup with asynchronous mode - and set to read-only, no listener configured and only those databases needed for reporting in the AG.
The reporting users would access the second replica instance directly - the application would access the primary replica using the listener.
To create a reporting users - create the login on the read-only secondary, grab the SID - create that login on the primary and add the user to the appropriate database with read-only permissions (e.g. db_datareader, view definition, grant execute on procedures, etc...). The user will be created and replicated to both secondary replicas - and will tie to the login on the read-only secondary because they match by SID.
Then - either disable or remove the login from the primary as it is no longer needed and you don't want the users logging into the primary instance.
Using this configuration, you can save some money by not sizing the read-only secondary the same as the primary and HA replica. You also don't need to worry about failover - reporting users always access the read-only secondary, and if that system is not available for some reason then the only affect is that reporting is down. If the system cannot be recovered you haven't impacted production in any way - and worse case, you evict the node, rebuild the server, add the new node - and setup the read-only AG again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 24, 2021 at 6:30 pm
I would not utilize read-intent for this scenario - that really should only be used for offloading application reads and is not intended to offload reporting users access.
Instead - upgrade to Enterprise Edition and create a 3-node cluster with 2 secondary replicas. The first replica will be used for HA - with an AG containing all databases, a listener and automatic failover (synchronous mode) and not set to read-intent or read-only. The second replica would then be setup with asynchronous mode - and set to read-only, no listener configured and only those databases needed for reporting in the AG.
The reporting users would access the second replica instance directly - the application would access the primary replica using the listener.
To create a reporting users - create the login on the read-only secondary, grab the SID - create that login on the primary and add the user to the appropriate database with read-only permissions (e.g. db_datareader, view definition, grant execute on procedures, etc...). The user will be created and replicated to both secondary replicas - and will tie to the login on the read-only secondary because they match by SID.
Then - either disable or remove the login from the primary as it is no longer needed and you don't want the users logging into the primary instance.
Using this configuration, you can save some money by not sizing the read-only secondary the same as the primary and HA replica. You also don't need to worry about failover - reporting users always access the read-only secondary, and if that system is not available for some reason then the only affect is that reporting is down. If the system cannot be recovered you haven't impacted production in any way - and worse case, you evict the node, rebuild the server, add the new node - and setup the read-only AG again.
Yeah, what he said.
While a read-only secondary with routing set up can certainly be used for reporting, Jeffrey's architecture makes more sense. We have read-only routing set up in our environment, but the reporting connect strings refer directly to the secondary.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply