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