1

I have 2 nodes in an AG. Non-readable secondary, sync commit mode.

There are sql agent jobs setup on both machines. The schedules are turned off on secondary so those jobs wont run. If there is failover then we manually enable the schedules for jobs on secondary.

The schedules run various jobs almost every minute.

When the primary goes down (technically because of network issue), but the machine is running, how do I immediately prevent the scheduled sql agent jobs on primary from running?

2
  • "how do I immediately prevent the scheduled sql agent jobs from running?" - to clarify, you mean on the primary replica that no longer has network access?
    – J.D.
    May 6 at 11:54
  • 1
    Yes I have updated now in question as well.
    – variable
    May 6 at 12:23

3 Answers 3

4

Wrap T-SQL of your jobs into this code:

if  ((select primary_replica from sys.dm_hadr_availability_group_states) = @@SERVERNAME
    and (select primary_recovery_health_desc from sys.dm_hadr_availability_group_states)  = 'ONLINE'
    and (select synchronization_health_desc from sys.dm_hadr_availability_group_states)  = 'HEALTHY')
begin
    <T-SQL here>
end

This way T-SQL will only run if SQL Server is in primary role, online and synchronization is healthy

See the https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-availability-group-states-transact-sql?view=sql-server-ver16

6
  • 1
    You should also have an exact copy of those jobs on the secondary so if there is a failover, the job will start running from the new primary. May 6 at 14:49
  • @DominiqueBoucher absolutely May 6 at 16:16
  • Isn't sys.fn_hadr_is_primary_replica more appropriate for the check?
    – variable
    Jul 16 at 16:36
  • @variable sys.fn_hadr_is_primary_replica only shows 1 or 0, while sys.dm_hadr_availability_group_states shows addition information related to state and sync status (online, healthy) Jul 18 at 12:43
  • If you could give the official documentation link for this then I will mark as answer
    – variable
    Jul 18 at 12:48
0

You can use the built in system function inside the job step to check if the replica is primary.

sys.fn_hadr_is_primary_replica(‘<dn_name>’)

If 1 is returned, do something, else exit the job.

IF COALESCE(sys.fn_hadr_is_primary_replica(‘<dn_name>’),1) = 1
BEGIN
    Do something…
END

COALESCE accounts for the function returning NULL, which will be the case if the database passed to the function is not part of an availability group.

0

We keep our job definitions same across all the nodes in the AG (Primary and Secondary's) including their schedules. But the only thing we add to our job's is one step Test write status before actual job step.

The Test write status does this:

IF (DATABASEPROPERTYEX('ag_database', 'Updateability') <> 'READ_WRITE')
BEGIN
    PRINT 'EXITING GRACEFULLY';
    THROW 51000, 'This is not a writeable replica', 1;
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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