3

I have SQL Jobs that need to only on primary replica on AlwaysOn Availability Group. When jobs were originally created ServerA was primary, at later point ServerB became primary so jobs that were on ServerA failed and jobs had to be manually recreated on ServerB to run properly.

What is the way to have jobs run only on Primary replica of AlwaysOn Availability Group?

Vladimir Oselsky
  • 869
  • 5
  • 12
  • 21

1 Answers1

8

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.

Vladimir Oselsky
  • 869
  • 5
  • 12
  • 21