February 15, 2016 at 5:16 am
This is probably really obvious and I might just need a fresh pair of eyes to check it, but I have this SQL Agent job that I need to run only against the Primary replica in an AlwaysOn AG setup. It works perfectly when I run the script in Management Studio, but I get different results when I execute it as a job.
use MyDB
go
if (select
ars.role_desc
from sys.dm_hadr_availability_replica_states ars
inner join sys.availability_groups ag
on ars.group_id = ag.group_id
where ag.name = 'OBFUSCATED_AG'
and ars.is_local = 1) = 'PRIMARY'
begin
EXECUTE my_stored_procedure
end
else
print 'This is not the primary replica.'
When I run that as a script on the primary replica, it succeeds:
Command(s) completed successfully.
When I run it as a script on the secondary replica, it succeeds:
This is not the primary replica.
But when I put that script into a SQL Agent job step and execute it on the primary replica, the job reports success and outputs
Executed as user: [Obfuscated\User]. This is not the primary replica
But it is the primary replica. What am I missing? I've checked all the brackets :-D. There are no other job steps.
Thanks
February 15, 2016 at 9:48 am
Does the [Obfuscated\User] account have sufficient privileges to query the DMVs and return results?
February 16, 2016 at 1:26 am
Hi, yes it does, it has view server state.
Thanks for the reply.
February 16, 2016 at 8:31 am
I saw this from Patrick Keisler: http://www.patrickkeisler.com/2013/07/are-you-primary-replica.html
He's querying the role column for a 1, and while that shouldn't matter, I'm wondering if there is some context that's different. Obviously you have security, but could there be some other issue here with what is being queried. You have the USE in the job step? What if you qualified for the database?
Could this return more than one row, which might mean that you are getting some different row first or last?
Also, perhaps you debug by having the job insert the results of a query against these DMVs into a table to comparison with what you get.
February 16, 2016 at 8:51 am
I did a bit of testing in my environment and I believe this also requires VIEW ANY DEFINITION permissions on the instance. This MSDN article seems to corroborate that.
February 17, 2016 at 4:28 am
Thanks, both. I'll have a look and get back to you.
February 22, 2016 at 6:37 am
Did you ever resolve this?
Since this is the 2014 forum, I'll assume you're on SQL 2014 and suggest looking at the new system function:
sys.fn_hadr_is_primary_replica
https://msdn.microsoft.com/en-us/library/dn249345.aspx
it might ease your coding and/or permissions
February 22, 2016 at 7:35 am
Hi. I've looked at that, and you're right, I'm on SQL 2014, but thanks. I haven't hada chance to make any progress with this yet because I'm working on it when I don't have anything going on in Production. I do appreciate the offers of help though, thanks all.
February 22, 2016 at 11:09 am
On the JOB
On the Job Steps Properties
in the General Section
in the Database do you have MASTER or a a different database?
If you have a different database that is part of the availability group it will not work.
You might want to use MASTER.
Also on your script when you have USE MYDB
if MYDB is part of the availability group it will give you an error.
You need to type USE MASTER
and use 3-part naming convention when calling Databases and Tables etc.
Try PARSE (on the left side of the job script) to see if it gives you an error before you save the job.
Hope this helps.
February 23, 2016 at 9:28 am
Have you tried pointing it to the listener DNS name?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply