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?

2
  • Are you using a failover cluster instance (FCI), or a Windows Server Failover Cluster (WSFC) and Availability Groups? Being explicit helps ("HA Cluster" is quite overloaded). Commented May 29, 2014 at 13:31
  • Ok well the FCI isn't really relevant in this case - basically you have jobs that need to follow the primary replica of an Availability Group. I think that's how you should frame the question so that it's more straightforward. Commented May 29, 2014 at 14:05

1 Answer 1

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.

2
  • 5
    You need to clarify that you need to create the same job on all of the servers that are potential primary replicas. Also you should make sure that your query limits the result to a specific availability group - you may only have one currently, but what happens when you have more than one? Won't that query pick one arbitrary row out of several? Commented May 29, 2014 at 14:41
  • 1
    This will not work if the step executes as a non-admin user, unless you explicitly add special privileges. See here for details.
    – takrl
    Commented Jun 25, 2014 at 13:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.