March 22, 2018 at 6:32 pm
I have a problem that a job configured on the second node of an AG CLuster is failing with errorThe SQL Agent JOb basically an SSIs package from MSDB with a Service account is failing.
It was originally set up with NOde 1 as primary and node2 is secondary.
When node 2 is failed over to make it primary the job on Node 2 is failing .
.-----
I saw this on the web , but, I am not sure if one needs to configure it like this for ALL jobs:
Does anyone knows/have an alternative way to do this?
Thanks for any help
March 23, 2018 at 3:07 am
Make the first step of the SQLAgent job a query against sys.fn_hadr_is_primary_replica to see if the database hosted locally is the primary or the secondary, and then use that result to either launch the SSIS package or to not do that.
Thomas Rushton
blog: https://thelonedba.wordpress.com
March 23, 2018 at 12:10 pm
Well , after I failed over the primary to the second node , I tried to execute the sql agent job manually on the second node.
It failed with an error saying that the db is in AG group etc...
But the same job executes on Node 1 ( run on Node 1) successfully when I fail over the AG to Node 1.
So, I am not sure why I need to check for that .
March 26, 2018 at 10:59 am
dvprao - Friday, March 23, 2018 12:10 PMWell , after I failed over the primary to the second node , I tried to execute the sql agent job manually on the second node.
It failed with an error saying that the db is in AG group etc...But the same job executes on Node 1 ( run on Node 1) successfully when I fail over the AG to Node 1.
So, I am not sure why I need to check for that .
add a first step to each job that queries to check whether its running on primary or secondary replica.
If not primary job should exit at step1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 26, 2018 at 12:58 pm
dvprao - Thursday, March 22, 2018 6:32 PMI have a problem that a job configured on the second node of an AG CLuster is failing with errorThe SQL Agent JOb basically an SSIs package from MSDB with a Service account is failing.
It was originally set up with NOde 1 as primary and node2 is secondary.
When node 2 is failed over to make it primary the job on Node 2 is failing ..-----
I saw this on the web , but, I am not sure if one needs to configure it like this for ALL jobs:
Does anyone knows/have an alternative way to do this?Thanks for any help
If you saved SSIS package in Integration Services Catalog on primary you have to copy it to secondary Integration Services Catalog.
If you saved it to disk on primary then copy it to the same location on secondary and then use fn_hadr_group_is_primary function.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply