0

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 ?

1 Answer 1

1

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/

2
  • This function can return NULL also, if the database is not in AG group. Commented Jan 13, 2022 at 18:03
  • Thanks. I'm trying to think of a way to not modify the code. but when we set the "database" to be used in the job, I don't think theres something we can do on the function for it to understand the database that it should use right?
    – Racer SQL
    Commented Jan 13, 2022 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.