November 28, 2014 at 8:39 am
Trying to work out if it is possible easily to direct particular users/groups to a SQL Server AlwaysOn read only node outside the Application intent setting.
I want to allow/force people access to the read only databases via SSMS but concerned they will not remember to use the "Application Intent" Read-only setting all the time.
I've seen some mention of AG routing but can't find anything that would do this on a user/group level.
Many thanks
Ian
November 30, 2014 at 4:17 am
The routing configuration only details the routing preferences for replicas when in the secondary role.
Users not only need to set the readonly application intent but also use the listener name and database name.
Maybe a logon trigger that checks the current role (primary or secondary) and allows access based on this may be an option
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 1, 2014 at 12:15 am
I've been looking into this too lately. Could Resource Governor be used to point logins / groups to the read-only Servers? or can it be used to specify the "application intent"?
Is it possible to set the 'execution context' of a stored procedure / adhoc query via a trigger?
December 1, 2014 at 3:48 am
Perry
Thanks for the response, sounds like something I can use with the logon trigger.
Appreciate your time to answer.
Ian
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply