Availability groups and sql agent

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    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

  • Jeffrey Williams

    SSC Guru

    Points: 88654

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • as1981

    SSCrazy

    Points: 2761

    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.

  • Jeffrey Williams

    SSC Guru

    Points: 88654

    as1981 wrote:

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • as1981

    SSCrazy

    Points: 2761

    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.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    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

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    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?

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    they vary, some are just overnight, some are hourly, some every 15 minutes

    MVDBA

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply