When we have SQL Server job running for databases in Always-On there could be need of running a specific job on Primary database only.
In such cases we are required to create the job on every server and add the logic that it runs only on the Primary server. The below template can be used for that. GitHub link.
-- SQL Agent Job Template for Always-On environment ------------------------- -- Remove Line 9 and Replace 10-16 with your code -- I prefer adding this information into the log that this job is skipped as the server is not PRIMARY DECLARE @AORole VARCHAR(20) = 'PRIMARY'; DECLARE @Message NVARCHAR(100) = 'Executing is running on Primary' IF @AORole = (SELECT role_desc FROM sys.dm_hadr_availability_replica_states WHERE is_local=1 AND role=1) PRINT 'Primary Server' /* Your Job code here . . . . */ ELSE EXEC xp_logevent 60000, @Message, informational