Thanks for reading, here is what i need to figure out:

Below script is what i think might fix my issue. I know how to do them individually, but i want them to run as whole.I'll use them in a job or automation in case if the databases on my AlwaysOn Availability group is not a primary, then the sql agent or agent XPs should turn off. This will help me to stop simultaneously jobs running on both Primary and Secondary.

  • In short, i need a script to turn off SQL agent when the role from primary change, and turn back ON when the role is primary for always on availability group.

I have two instance, A primary, and B secondary. I have setup everything on my AOAG for my production including backups, however this issue come to my mind and don't wanna do it manually ( not nice ).

Thank you,

Here is what i have

use master 

if sys.fn_hadr_backup_is_preferred_replica ('HardDB') = 0 --- If the Database is not a primary replica and is 0, then 
sp_configure 'show advanced options', 1 -- trun on the advanced options
reconfigure ; 
sp_configure 'agent XPs' , 1 -- Then, off the SQL Agent XPs 

sp_configure 'show advanced options', 0 --- then, trun back off the advanced options. 
  • 1
    I don't understand why you can't use the built in functions for your jobs which would accomplish basically the same thing? technet.microsoft.com/en-us/library/dn249345.aspx – Sean Gallardy Nov 29 '16 at 17:13
  • As a concept this is horrible. In particular doing this would mean that your system databases would not get backed up, and other, non AG related routines would not get executed. You should check and run things depending upon the current state of the AG. Additionally you could set your AG to run backups off the secondary, and this would kill the Agent on your primary. – Nic Nov 29 '16 at 18:06
  • @ Sean, Nic. Thank you both, wise advice. I have found some script with works find, and runs the job if the node is primary. else, will fail silently. Below, I have posted the script. – reshad Nov 30 '16 at 18:55

We use a 'Primary checker' on all of our agent jobs on both nodes as Step 1. It validates that the DB is, in fact, the primary.

DECLARE @database_name NVARCHAR(50)

SET @database_name = (Select name from sys.databases where database_id = 7) 

SELECT adc.database_name
FROM sys.dm_hadr_availability_replica_states AS ars
INNER JOIN sys.availability_databases_cluster AS adc ON ars.group_id = adc.group_id
WHERE (ars.is_local = 1)
AND (ars.role_desc = 'PRIMARY')
AND (adc.database_name = @database_name)
FROM sys.databases
WHERE [name] = @database_name
AND replica_id IS NULL
PRINT @database_name
RAISERROR ('This is not the server you are looking for...',16,1)

We then set the on failure action for this step to 'quit the job reporting success', which eliminates job failure alerts and allows it to fail silently on the first step if it isn't the primary.

This way, your Agent is never down, jobs just won't start until they are the primary.

  • @Kris, Thanks for the wise advice. I have tested your script, and configured a test job, however the error will rise if the node is secondary. I was looking, as you said, to fail silently. Just to note, I have configured the first step which contains the above script with "Quit the job reporting success" on the failure. – reshad Nov 30 '16 at 18:47
  • It will still show as a failure but won't be picked up by alerting or email anyone. If it ran correctly, you'll see a yellow triangle on the job, a red X on the step but it doesn't treat it as a job 'failure' – Kris Gruttemeyer Nov 30 '16 at 20:56

we use a simple primary node checker to only run jobs on the primary node of an AlwaysOn cluster. It's a little flaky with some things like SSIS packages so in those cases we have the first step as a tester and if the node is primary then proceed to step 2 and so on. If it's passive then stop the job.

For @dbname use a variable that is sysname or just hard code a database name

  IF (SELECT master.sys.fn_hadr_is_primary_replica (@dbname)) = 1
  <do your stuff>

This is looking for the primary replica, is so, then will run the job, else it will fail silently.

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'
    --Logic for the job goes here

Source: Google, someone had posted.


How about an update trigger on [sys].[dm_hadr_database_replica_states], that can run, disable, enable agent job on respective hosts ?

  • 1
    I believe a trigger on a view is only activated if someone tries to execute an UPDATE statement against the view itself; it's not run when the tables the view uses have data inserted, updated, or deleted. – RDFozz Jul 11 '18 at 19:39

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.