SQL Server Agent Jobs and Availability Groups

When using Availability Groups (or database mirroring for that matter), all instance level objects like logins, are not automatically distributed across all participating instances. The same goes for SQL Agent jobs. All of these objects need to be replicated somehow. Besides that, SQL Agent jobs must be “Primary or not”-aware. One simple solution to this problem, is to insert a step in each job, which should run first, with the following code. Set this step to continue with the next step on success, and to quit the job reporting success on a failure.

IF (SELECT ARS.role_desc

FROM sys.dm_hadr_availability_replica_states AS ARS

INNER JOIN sys.availability_groups AS AG ON ARS.group_id = AG.group_id AND ARS.is_local = 1) <> 'PRIMARY'

BEGIN

--We're on the secondary node, throw an error

THROW 50001, 'Unable to execute job on secondary node',1;

END