March 2, 2020 at 10:45 am
I have A proof of concept 2019 sql server with 2 active nodes and a read only reporting node.
I'm looking at the SQL agent jobs and don't want them to fire on both nodes - i know the jobs need to be on both nodes for a failover scenario
so my plan was to put something like
DECLARE @role VARCHAR(50)
SELECT @role = [role_desc] FROM [sys].[dm_hadr_availability_replica_states] hars
INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] =[DatabaseName]
IF @role = 'PRIMARY'
BEGIN
PRINT 'Primary - Job can proceed'
END
ELSE
BEGIN
-- Deliberately cause a Failure
SELECT 1/0
END
as the first step in every job - problem is that I have more than 400 sql agent jobs from the initial migration.
question 1 … anyone got a better idea?
question 2 …. anyone got a clever script to do an add step 1 to every job with this kinda stuff in and not break everything else
question 3... monitoring, I don't want emails because the job is starting and failing on the secondary
just looking for advice
MV
MVDBA
March 2, 2020 at 6:53 pm
Don't fail the jobs on the non-active instance...that just introduces a lot of error messages and problems. Instead - check for the node being primary and if so...execute the task(s).
This can be handled using a function...for example, in job step 1 of Job A:
if ( dbo.fnAGPrimaryDB() = 1 ) Execute dbo.MyProcedure;
The other option is to add a new job step - setting the job step to quit with success on failure. On success - the job step will be set to go to next step. Updating all of the jobs to have this step will require manual intervention because you have to reset the start job step - insert the new job step and set the appropriate on success/failure actions.
With the first option - it can be scripted to modify the job step to include the function call...and the only jobs that would need additional manual effort are those jobs that are not calling a stored procedure.
If the jobs are SSIS - then you have another option. You can put in a step in each package that checks if it is the primary node (same function) and only continue to the rest of the package if running on the primary. Or - you can insert a job step...but that would be a manual effort.
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 2, 2020 at 7:47 pm
The other option is to add a new job step - setting the job step to quit with success on failure. On success - the job step will be set to go to next step.
I didn't have the problem of lots of existing jobs to update but I used the above option on several new jobs and never had an issue with it. I found it useful because you can still fail the job if another step fails for another reason.
March 2, 2020 at 8:41 pm
Jeffrey Williams wrote:The other option is to add a new job step - setting the job step to quit with success on failure. On success - the job step will be set to go to next step.
I didn't have the problem of lots of existing jobs to update but I used the above option on several new jobs and never had an issue with it. I found it useful because you can still fail the job if another step fails for another reason.
The first option will still fail the job and/or step - if it executes the procedure/code. The function just checks to see if this is the primary and if not...it doesn't do anything.
Either method works...but the second method requires setting the on success/failure actions appropriately as well as inserting a new job step in every job and resetting the start job step to the newly inserted step.
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 2, 2020 at 8:53 pm
Sorry I wasn't intending to suggest that one method is better than the other.
I could only comment on the second method as that is the only one I've used.
Apologises for any confusion caused.
March 3, 2020 at 8:52 am
Hi jeffrey
All good points, a lot of these jobs are multi step - I think i'll test the option to quit with sucess
still - 400 jobs 🙁
MVDBA
March 9, 2020 at 2:24 pm
How often do the jobs have to run? If it's once a day (say), could you create a master job on each node that checks primary/secondary and then only triggers the other jobs on the primary replica?
March 9, 2020 at 2:41 pm
they vary, some are just overnight, some are hourly, some every 15 minutes
MVDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply