April 12, 2015 at 10:36 am
Hi All,
We are in process of implementing AG in SQL 2014. I am facing issues while checking jobs.
I have created the jobs on both nodes. However, I want the jobs to be executed only on primary.
For this, I started introducing a new step inside the sql agent job which will be my 1st step which checks whether it is Primary or not.
If not primary, I want the job to quit with Success else I want to proceed to next step as it is Primary.
But i wasn't able to adjust the Advanced option of 1st step where i am checking whether isPrimary or not. Can we adjust the advanced options
of setting @on_success_step_id programmatically in 1st step itself... or Is there a better way to do this.
If anyone share that piece of logic would be a great help.
Thanks in Advance.
April 12, 2015 at 10:45 am
if its not the primary just use sp_stop_job on the job name\id
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 12, 2015 at 11:04 am
Thanks for the quick reply Sir. Thing is that I would like to implement such a logic, which doesn't even let the job to start if it is not primary... Anyway to do that?
April 12, 2015 at 11:18 am
Hi Perry,
Your advise is working like a charm. Thank you Sir 🙂
April 12, 2015 at 11:30 am
youre welcome
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 12, 2015 at 12:13 pm
Sir, I have a small issue with this actually. when I look at job history it shows me a stopped in red marks and it doesn't look good if someone view's history of that job. Also it might be picked up by SCOM Alerts which I don't want. Any other way? I mean as of now, the code works perfectly fine but is there any other options we can look at?
April 13, 2015 at 3:49 am
the only other option I can think of immediately is a parent job which runs and checks whether the AO replica is primary or not. If primary it calls the child job if not it doesn't 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 13, 2015 at 8:46 am
That's a good option to consider as well. Thank you sir for the continuous help.
April 13, 2015 at 3:12 pm
What we do in the first step is raise an error to force the step to fail
Test for Secondary for the AG
....
If @Secondary = 1
BEGIN
RAISERROR(''Running on Secondary Replica.'', 11, 1)
END
Then set the Advanced option for step 1 to: On Failure - Quite the job Reporting Success
This means the Job fails gracefully and reports as succeeding, no problem for SCOM or most other alerts that are triggered by failures at the Job level.
Cheers
Leo
No solution in IT is so complex that you can't find a client who wants a more complex solution.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
April 14, 2015 at 5:34 am
Leo,
Thank you for your input. Could you please share the create script for any test job which has includes above functionality so that I can repro at my end?
-Sam
April 15, 2015 at 9:36 am
firstly, this is a shame for microsoft. they have new feature but their own product does not support it 100%.
anyway, at the beginning i tried the same way mentioned above but it has side effects. then i came up with a different idea. below is a sp which runs every 1 min and changes the enabled/disabled marks of the jobs according the their description text. i hope it helps somebody out.
USE [msdb]
GO
ALTER PROCEDURE [dbo].[spChangeJobsAG] as
begin
declare @cjobname varchar(max),@cdescription varchar(max),@cstatus int
declare cur cursor for
SELECT name,description,enabled FROM msdb.dbo.sysjobs
open cur
fetch next from cur into @cjobname,@cdescription,@cstatus
while @@FETCH_STATUS=0
begin
if master.dbo.svf_AgReplicaState('AG_NAME')=1
begin
if CHARINDEX('PRIMARY_ONLY',@cdescription)<>0 EXEC dbo.sp_update_job @job_name = @cjobname,@enabled = 1 ;
if CHARINDEX('SECONDARY_ONLY',@cdescription)<>0 EXEC dbo.sp_update_job @job_name = @cjobname,@enabled = 0 ;
end else
begin
if CHARINDEX('PRIMARY_ONLY',@cdescription)<>0 EXEC dbo.sp_update_job @job_name = @cjobname,@enabled = 0 ;
if CHARINDEX('SECONDARY_ONLY',@cdescription)<>0 EXEC dbo.sp_update_job @job_name = @cjobname,@enabled = 1 ;
end
if CHARINDEX('DISABLED',@cdescription)<>0 EXEC dbo.sp_update_job @job_name = @cjobname,@enabled = 0 ;
if CHARINDEX('ENABLED',@cdescription)<>0 EXEC dbo.sp_update_job @job_name = @cjobname,@enabled = 1 ;
fetch next from cur into @cjobname,@cdescription,@cstatus
end
close cur
deallocate cur
end
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply