How to prevent primary node's SQL jobs from running when the primary node goes into resolving state?
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?
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 end
This way T-SQL will only run if SQL Server is in primary role, online and synchronization is healthy