T-SQL: Only run SQL agent job on primary node

In a SQL Always On setup, the SQL agent jobs are not synced between the different nodes. This means you need to manually create them on the other nodes. For read-write jobs, you can only run them on the active node of course. You should modify the SQL agent job a little bit in order to check if the database has the primary role, and only execute the code if that’s the case. The code below can be used for this.
The first part will create a function in your master database, which then can be called by a step within the sql agent job.

USE [master]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_is_writeable_replica]    Script Date: 15/02/2023 12:24:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 
CREATE FUNCTION [dbo].[fn_is_writeable_replica] (@dbname sysname)
RETURNS BIT
WITH EXECUTE AS CALLER 
AS 
 
BEGIN 
 
      DECLARE @is_writeable BIT;
 
      IF EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = @dbname)
            BEGIN
                  IF (DATABASEPROPERTYEX(@dbname, 'Updateability') <> 'READ_WRITE')
                        SELECT @is_writeable =  0
 
            ELSE
                  SELECT @is_writeable =  1
            END
      ELSE
            BEGIN
                  SELECT @is_writeable =  0
            END
 
      RETURN(@is_writeable);
 
END
GO


If sys.fn_hadr_is_primary_replica ( 'MY_DB' ) <> 1  
BEGIN 
    -- This is not the primary replica, exit without error. 
	 print 'Secondary node'
END 
-- This is the primary replica, continue to run the job... 
else
begin
  --YOURACTIONS HERE
end
Share your love