How to identify primary/secondary AOAG when running jobs in certain databases?



  • we have 3 servers, 1 one of them host a primary and a secondary aoag, 2 also hosts a primary ( secondary on node 1 ) and a secondary (primary on node 1). 3 is just DR. All of them have the same jobs.

    I'm trying to understand how can we run jobs on certain databases and determine if this database is primary on this server or not. I would like to achieve this without hardcode anything.

    I have these 2 queries but they only used to work when I have an entire server as primary or secondary:

    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 = 'YourAvailabilityGroupName'
        and ars.is_local = 1) = 'PRIMARY'
    begin
        -- this server is the primary replica, do something here
    end
    else
    begin
        -- this server is not the primary replica, (optional) do something here
    end
    

    --ou
    DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
    DECLARE @RoleDesc NVARCHAR(60)

    SELECT @RoleDesc = a.role_desc
    FROM sys.dm_hadr_availability_replica_states AS a
    JOIN sys.availability_replicas AS b
    ON b.replica_id = a.replica_id
    WHERE b.replica_server_name = @ServerName

    IF @RoleDesc = 'PRIMARY'
    BEGIN
    PRINT 'OK. NEXT...'
    END
    else
    RAISERROR ('ERROR. JOB IS NOT GOING TO RUN BECAUSE THIS IS NOT THE PRIMARY INSTANCE.', -- Message text.
    16, -- Severity.
    1 -- State.
    );

    In this case now, I have multiple databases being primary and secondary (not readable).

    so when runnin a job in database X I would like to run a function for example to determine that this database is not primary on that server, so the job stops:

    for example:

        IF cool_function_primary_or_secondary() <> 'PRIMARY' 
    BEGIN
    do my stuff
     msdb..sp_stop_job @job_name=my job 
    END
    

    this will not fail the job.

    and, I would need to set the database to the database that will run the job to execute this right? also, if it's not accessible, is it going to fail ?


  • QA Engineer

    The function you need to use is sys.fn_hadr_is_primary_replica (2014 and higher). You just pass it a database name and it returns either 1 or 0.

    I outlined one way to make SQL Agent jobs AG-aware by adding a job step using that function.

    https://www.patrickkeisler.com/2020/05/making-sql-agent-jobs-aware-of-availability-groups/

    Wayne Sheffield also listed a few additional options.

    https://blog.waynesheffield.com/wayne/archive/2018/04/primary-replica-jobs/


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2