Skip to main content
added information from the comments
Source Link
Vladimir Oselsky
  • 869
  • 5
  • 12
  • 21

An easy way to get job to execute only on primary node is to put simple check for the job to verify which node is 'PRIMARY' at the time job is being executed. It can be done with simple sql statement:

DECLARE @ServerName NVARCHAR(256)  = @@SERVERNAME 
DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
    FROM sys.dm_hadr_availability_replica_states AS a
    JOIN sys.availability_replicas AS b
        ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName

IF @RoleDesc = 'PRIMARY'
BEGIN
    --Logic for the job goes here
END

Now just place logic between BEGIN and END keywords.

Just like Aaron pointed out in the comments, a job still has to be created on all servers with the same logic to ensure that it only runs on primary replica and not on the secondaries. There are scripts and tools that can be used to copy jobs from primary replica to the secondary replicas.

An easy way to get job to execute only on primary node is to put simple check for the job to verify which node is 'PRIMARY' at the time job is being executed. It can be done with simple sql statement:

DECLARE @ServerName NVARCHAR(256)  = @@SERVERNAME 
DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
    FROM sys.dm_hadr_availability_replica_states AS a
    JOIN sys.availability_replicas AS b
        ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName

IF @RoleDesc = 'PRIMARY'
BEGIN
    --Logic for the job goes here
END

Now just place logic between BEGIN and END keywords.

An easy way to get job to execute only on primary node is to put simple check for the job to verify which node is 'PRIMARY' at the time job is being executed. It can be done with simple sql statement:

DECLARE @ServerName NVARCHAR(256)  = @@SERVERNAME 
DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
    FROM sys.dm_hadr_availability_replica_states AS a
    JOIN sys.availability_replicas AS b
        ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName

IF @RoleDesc = 'PRIMARY'
BEGIN
    --Logic for the job goes here
END

Now just place logic between BEGIN and END keywords.

Just like Aaron pointed out in the comments, a job still has to be created on all servers with the same logic to ensure that it only runs on primary replica and not on the secondaries. There are scripts and tools that can be used to copy jobs from primary replica to the secondary replicas.

Source Link
Vladimir Oselsky
  • 869
  • 5
  • 12
  • 21

An easy way to get job to execute only on primary node is to put simple check for the job to verify which node is 'PRIMARY' at the time job is being executed. It can be done with simple sql statement:

DECLARE @ServerName NVARCHAR(256)  = @@SERVERNAME 
DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
    FROM sys.dm_hadr_availability_replica_states AS a
    JOIN sys.availability_replicas AS b
        ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName

IF @RoleDesc = 'PRIMARY'
BEGIN
    --Logic for the job goes here
END

Now just place logic between BEGIN and END keywords.