HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Scheduling jobs on a SQL Always On HA cluster

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
schedulingsqlalwaysjobscluster

Problem

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?

Solution

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 !).

Code Snippets

-- 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

Context

StackExchange Database Administrators Q#129556, answer score: 4

Revisions (0)

No revisions yet.