How to run a SQL Agent job only on the primary Always On 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 is the case. The code below can be used for this.
The first part will create a function in your master database, which can then 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