Scheduling jobs on a SQL Always On HA cluster

Question

When scheduling jobs using SQL Server Agent for AOHA cluster, does one use a Master-Target config as you would as if the servers were separate? Or is there a better way to distribute the jobs to all of the servers so that they can be picked up by one of the replicas in case the primary server fails?

Answer 1

We create the jobs (and sync them as well) across all the instances that are part of AlwaysON AG.

There is an additional logic that you should put in the job to check if the instance is primary and then only run the job. A pseudo code would be like below :

-- http://dba.stackexchange.com/a/45152/8783
if (select
        ars.role_desc
    from sys.dm_hadr_availability_replica_states ars
    inner join sys.availability_groups ag
    on ars.group_id = ag.group_id
    where ag.name = 'YourAvailabilityGroupName'
    and ars.is_local = 1) = 'PRIMARY'
begin
    -- this server is the primary replica, do something here
end
else
begin
    -- this server is not the primary replica, (optional) do something here
end

To sync logins, jobs, etc, you can use PowerShell or this utility from SQLSkills (not sure if it works for 2014 !).

Answer 2

The difficult part with SQL Agent jobs on Always On Availability Groups (AG) instances is that you usually want the job to run only if the node it is on is the PRIMARY.

You want the jobs to exist on each node so that they are there in the event of a fail over.

I've found making the Agent Jobs "AG aware" is the easiest approach. I talk about a similar situation here.

The general idea is to add a piece of code to each Agent job that checks to see if it's node is currently the PRIMARY. If it is then run the job, if not stop executing.

The code below (modified from my blog post) should help:

DECLARE @Role NVARCHAE(120);

SELECT @Role = ars.role_desc
FROM sys.availability_replicas ar
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
JOIN sys.availability_groups ag ON ars.group_id = ag.group_id
WHERE ag.name = 'YOUR AG'
AND ars.is_local = 1;

IF @Role = 'PRIMARY'
BEGIN;
  Your SQL Agent code
END;

Answer 3

Create a function as:

CREATE FUNCTION is_primary(@DBname NVARCHAR(256))
RETURNS int
AS
BEGIN
IF DB_ID(@DBName)>4
BEGIN
DECLARE @Servername NVARCHAR(256)
SET @Servername=@@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
SELECT @RoleDesc=hars.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] =@DBName AND DB_ID(adc.[database_name])>4
DECLARE @is_primary int
IF @RoleDesc='PRIMARY'
SET @is_primary=1
ELSE SET @is_primary=0
RETURN(@is_primary);
END
ELSE
BEGIN
SET @is_primary=-1
END
RETURN(@is_primary);
END

While creating the job, keep this line before the T-SQL statement of the job:

IF (SELECT dbo.is_primary('<yourdbname>'))=1
BEGIN
*****Job's Logic*****
END

The definitions are 1 for Primary, 0 for Secondary and -1 for System Databases.