I have A proof of concept 2019 sql server with 2 active nodes and a read only reporting node.
I'm looking at the SQL agent jobs and don't want them to fire on both nodes - i know the jobs need to be on both nodes for a failover scenario
so my plan was to put something like
DECLARE @role VARCHAR(50)
SELECT @role = [role_desc] FROM [sys].[dm_hadr_availability_replica_states] hars
INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] =[DatabaseName]
IF @role = 'PRIMARY'
PRINT 'Primary - Job can proceed'
-- Deliberately cause a Failure
as the first step in every job - problem is that I have more than 400 sql agent jobs from the initial migration.
question 1 … anyone got a better idea?
question 2 …. anyone got a clever script to do an add step 1 to every job with this kinda stuff in and not break everything else
question 3... monitoring, I don't want emails because the job is starting and failing on the secondary
just looking for advice