Script to turn off SQL agent when Primary role change to Secondary in AlwaysOn – sql2014

Posted on

Question :

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 
go 

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

sp_configure 'show advanced options', 0 --- then, trun back off the advanced options. 
reconfigure 
go

Answer :

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) 

IF EXISTS (
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)
)
OR EXISTS (
SELECT NAME
FROM sys.databases
WHERE [name] = @database_name
AND replica_id IS NULL
)
PRINT @database_name
ELSE
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.

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
  BEGIN;
  <do your stuff>
  end

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

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

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 ?

Leave a Reply

Your email address will not be published.