I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups. Maybe I missed something, however at the current state I feel that SQL Server Agent is not really integrated with this great SQL2012 feature.

How can I make a scheduled SQL agent job aware of a node switch? For example I have a job running on the primary node which loads data each hour. Now if the primary goes down, how can I activate the job on the secondary which now becomes primary?

If I schedule the job always on the secondary it fails because then the secondary is read-only.

up vote 38 down vote accepted

Within your SQL Server Agent job, have some conditional logic to test for if the current instance is serving the particular role you are looking for on you availability group:

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

All this does is pull the current role of the local replica, and if it's in the PRIMARY role, you can do whatever it is that your job needs to do if it is the primary replica. The ELSE block is optional, but it's to handle possible logic if your local replica isn't primary.

Of course, change 'YourAvailabilityGroupName' in the above query to your actual availability group name.

Don't confuse availability groups with failover cluster instances. Whether the instance is the primary or secondary replica for a given availability group doesn't affect server-level objects, like SQL Server Agent jobs and so on.

Rather than doing this on a per job basis (checking every job for the state of the server before deciding to continue), I've created a job running on both servers to check to see what state the server is in.

  • If its primary, then enable any job that has a step targeting a database in the AG.
  • If the server is secondary, disable any job targeting a database in the AG.

This approach provides a number of things

  • it works on servers where there are no databases in AG (or a mix of Db's in/out of AGs)
  • anyone can create a new job and not have to worry about whether the db is in an AG (although they do have to remember to add the job to the other server)
  • Allows each job to have a failure email that remains useful (all your jobs have failure emails right?)
  • When viewing the history of a job, you actually get to see whether the job actually ran and did something (this being the primary), rather than seeing a long list of success that actually didn't run anything (on the secondary)

the script checks the database in the field below if this database is in an Availability Group the script will take some action

This proc is executed every 15 mins on each server. (has the added bonus of appending a comment to inform people why the job was disabled)

/*
    This proc goes through all SQL Server agent jobs and finds any that refer to a database taking part in the availability Group 
    It will then enable/disable the job dependant on whether the server is the primary replica or not   
        Primary Replica = enable job
    It will also add a comment to the job indicating the job was updated by this proc
*/
CREATE PROCEDURE dbo.sp_HADRAgentJobFailover (@AGname varchar(200) = 'AG01' )
AS 

DECLARE @SQL NVARCHAR(MAX)

;WITH DBinAG AS (  -- This finds all databases in the AG and determines whether Jobs targeting these DB's should be turned on (which is the same for all db's in the AG)
SELECT  distinct
        runJobs = CASE WHEN role_desc = 'Primary' THEN 1 ELSE 0 END   --If this is the primary, then yes we want to run the jobs
        ,dbname = db.name
        ,JobDescription = CASE WHEN hars.role_desc = 'Primary'  -- Add the reason for the changing the state to the Jobs description
                THEN '~~~ [Enabled] using automated process (DBA_tools.dbo.sp_HADRAgentJobFailover) looking for jobs running against Primary Replica AG ~~~ '
                ELSE '~~~ [Diabled] using Automated process (DBA_tools.dbo.sp_HADRAgentJobFailover) because the job cant run on READ-ONLY Replica AG~~~ ' END 
FROM sys.dm_hadr_availability_replica_states hars
INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id
INNER JOIN sys.Databases db ON  db.replica_id = hars.replica_id
WHERE is_local = 1
AND ag.Name = @AGname
) 

SELECT @SQL = (
SELECT DISTINCT N'exec msdb..sp_update_job @job_name = ''' + j.name + ''', @enabled = ' + CAST(d.runJobs AS VARCHAR) 
                + ',@description = ''' 
                + CASE WHEN j.description = 'No description available.' THEN JobDescription -- if there is no description just add our JobDescription
                       WHEN PATINDEX('%~~~%~~~',j.description) = 0 THEN j.description + '    ' + JobDescription  -- If our JobDescription is NOT there, add it
                       WHEN PATINDEX('%~~~%~~~',j.description) > 0 THEN SUBSTRING(j.description,1,CHARINDEX('~~~',j.description)-1) + d.JobDescription  --Replace our part of the job description with what we are doing.
                       ELSE d.JobDescription  -- Should never reach here...
                    END 
                + ''';'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s
INNER JOIN DBinAG d ON d.DbName =s.database_name     
ON j.job_id = s.job_id
WHERE j.enabled != d.runJobs   -- Ensure we only actually update the job, if it needs to change
FOR XML PATH ('')
)
PRINT REPLACE(@SQL,';',CHAR(10))
EXEC sys.sp_executesql @SQL

Its not fool proof, but for overnight loads and hourly jobs it gets the job done.

Even better than having this procedure run on a schedule, instead run it in response to Alert 1480 (AG role change alert).

I'm aware of two concepts to accomplish this.

Prerequisite: Based on Thomas Stringer's answer, I created two functions in the master db of our two servers:

CREATE FUNCTION [dbo].[svf_AgReplicaState](@availability_group_name sysname)
RETURNS bit
AS
BEGIN

if EXISTS(
    SELECT        ag.name
    FROM            sys.dm_hadr_availability_replica_states AS ars INNER JOIN
                             sys.availability_groups AS ag ON ars.group_id = ag.group_id
    WHERE        (ars.is_local = 1) AND (ars.role_desc = 'PRIMARY') AND (ag.name = @availability_group_name))

    RETURN 1

RETURN 0

END
GO

CREATE FUNCTION [dbo].[svf_DbReplicaState](@database_name sysname)
RETURNS bit
AS
BEGIN

IF EXISTS(
    SELECT        adc.database_name
    FROM            sys.dm_hadr_availability_replica_states AS ars INNER JOIN
                             sys.availability_databases_cluster AS adc ON ars.group_id = adc.group_id
    WHERE        (ars.is_local = 1) AND (ars.role_desc = 'PRIMARY') AND (adc.database_name = @database_name))

    RETURN 1
RETURN 0

END

GO


  1. Make a job terminate if it's not executed on the primary replica

    For this case, every job on both servers needs either of the following two code snippets as Step 1:

    Check by group name:

    IF master.dbo.svf_AgReplicaState('my_group_name')=0
      raiserror ('This is not the primary replica.',2,1)
    

    Check by database name:

    IF master.dbo.svf_AgReplicaState('my_db_name')=0
      raiserror ('This is not the primary replica.',2,1)
    

    If you use this second one, beware of the system databases though - by definition they can not be part of any availability group, so it'll always fail for those.

    Both of these work out of the box for admin users. For non-admin users, you have to do add extra permissions, one of them suggested here:

    GRANT VIEW SERVER STATE TO [user];
    GRANT VIEW ANY DEFINITION TO [user];
    

    If you set the failure action to Quit job reporting success on this first step, you won't get the job log full of ugly red cross signs, for the main job they'll turn into yellow warning signs instead.

    From our experience, this is not ideal. We at first adopted this approach, but quickly lost track regarding finding jobs that actually had a problem, because all the secondary replica jobs cluttered the job log with warning messages.

    What we then went for is:

  2. Proxy jobs

    If you adopt this concept, you'll actually need to create two jobs per task you want to perform. The first one is the "proxy job" that checks if it's being executed on the primary replica. If so, it starts the "worker job", if not, it just gracefully ends without cluttering the log with warning or error messages.

    While I personally don't like the idea of having two jobs per task on every server, I think it's definetly more maintainable, and you don't have to set the failure action of the step to Quit job reporting success, which is a bit awkward.

    For the jobs, we adopted a naming scheme. The proxy job is just called {put jobname here}. The worker job is called {put jobname here} worker. This makes it possible to automate starting the worker job from the proxy. To do so, I added the following procedure to both of the master dbs:

    CREATE procedure [dbo].[procStartWorkerJob](@jobId uniqueidentifier, @availabilityGroup sysname, @postfix sysname = ' worker') as
    declare @name sysname
    
    if dbo.svf_AgReplicaState(@availabilityGroup)=0
        print 'This is not the primary replica.'
    else begin
        SELECT @name = name FROM msdb.dbo.sysjobs where job_id = @jobId
    
        set @name = @name + @postfix
        if exists(select name from msdb.dbo.sysjobs where name = @name)
            exec msdb.dbo.sp_start_job @name
        else begin
            set @name = 'Job '''+@name+''' not found.'
            raiserror (@name ,2,1)
        end
    end
    GO
    

    This utilizes the svf_AgReplicaState function shown above, you could easily change that to check using the database name instead by calling the other function.

    From within the only step of the proxy job, you call it like this:

    exec procStartWorkerJob $(ESCAPE_NONE(JOBID)), '{my_group_name}'
    

    This utilizes Tokens as shown here and here to get at the current job's id. The procedure then gets the current job name from msdb, appends  worker to it and starts the worker job using sp_start_job.

    While this is still not ideal, it keeps the job logs more tidy and maintainable than the previous option. Also, you can always have the proxy job run with a sysadmin user, so adding any extra permissions isn't necessary.

If the data load process is a simple query or procedure call then you can create the job on both nodes and let it determine if its the primary node based based on the Updateability property of the database, before it executes the data load process:

IF (SELECT CONVERT(sysname,DatabasePropertyEx(DB_NAME(),'Updateability'))) != 'READ_ONLY'
BEGIN

-- Data Load code goes under here

END

It is always better to create a new Job Step which checks if it is a Primary Replica then everything is fine to continue with the job execution else if it is a Secondary Replica then Stop the job. Do not fail the job else it will keep sending unnecessary notifications. Instead stop the job so that it is cancelled and no notifications are sent out whenever these jobs are executed on the Secondary Replica.

Below is the script to add a first step for a specific job.

Note to execute the script:

  • Replace 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' with Job_ID
  • Replace 'YYYYYYYYYYYYYYYYYYYYYYYYYY' with Job_Name
  • If there are multiple Availability Groups, then set the AG name in the variable @AGNameToCheck_IfMoreThanSingleAG as to which AG should be checked for its replica state.

  • Also note that this script should work well even on those servers which do not have availability groups. Will execute only for SQL Server versions 2012 and beyond.

            USE [msdb]
            GO
            EXEC msdb.dbo.sp_add_jobstep @job_id=N'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX', @step_name=N'CheckForSecondaryReplica', 
                    @step_id=1, 
                    @cmdexec_success_code=0, 
                    @on_success_action=3, 
                    @on_fail_action=2, 
                    @retry_attempts=0, 
                    @retry_interval=0, 
                    @os_run_priority=0, @subsystem=N'TSQL', 
                    @command=N'
            DECLARE @AGNameToCheck_IfMoreThanSingleAG VARCHAR(100)
            SET @AGNameToCheck_IfMoreThanSingleAG = ''AGName_IfMoreThanOneAG'' -- If there are Multiple AGs, then a single server can have Primary of one AG and Secondary of other. So Job creator has to define as to which AG needs to verified before the job is automatically run on Primary.
    
            DECLARE @NumberofAGs INT
            SELECT @NumberofAGs = COUNT(group_id) FROM sys.availability_groups ags
    
    
            IF(@NumberofAGs < 2)
                IF EXISTS(Select * FROM sys.dm_hadr_availability_replica_states hars WHERE role_desc = ''Secondary'' AND hars.is_local = 1)                 
                                    EXEC msdb.dbo.sp_stop_job N''YYYYYYYYYYYYYYYYYYYYYYYYYY'' ;
                                    --RAISERROR(''This is a Secondary Replica'',16,1)
    
            IF(@NumberofAGs >= 2)
                IF EXISTS(SELECT 1 FROM sys.availability_groups WHERE name = @AGNameToCheck_IfMoreThanSingleAG)
                BEGIN
                            IF EXISTS(Select * from  sys.availability_groups ag
                                            JOIN sys.dm_hadr_availability_replica_states hars
                                                        ON ag.group_id = hars.group_id
                                                        Where role_desc = ''Secondary''
                                                        AND hars.is_local = 1
                                                        AND ag.name = @AGNameToCheck_IfMoreThanSingleAG)
                            BEGIN
                                    EXEC msdb.dbo.sp_stop_job N''YYYYYYYYYYYYYYYYYYYYYYYYYY'' ;
                                    --RAISERROR(''This is a Secondary Replica'',16,1)
                            END
                END
                ELSE
                            BEGIN
                                    RAISERROR(''The Defined AG in the Variable is not a part of this Server. Please Check!!!!!!!!!!!'',16,1)
                            END', 
                    @database_name=N'master', 
                    @flags=0
            GO
    

Another way is to insert a step in each job, which should run first, with the following code:

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

Set this step to continue with the next step on success, and to quit the job reporting success on a failure.

I find it cleaner to add an extra step instead of adding extra logic to an existing step.

Another, newer option, is using master.sys.fn_hadr_is_primary_replica('DbName'). I have found this super helpful when using SQL Agent to do database maintenance (coupled with a cursor I've used for years) and also when executing an ETL or other database specific task. The benefit is that it singles out the database instead of looking at the whole Availability Group...if that's what you need. It also makes it much more improbable that a command will be executed against a database that "was" on the primary, but let's say an automatic failover happened during the job execution, and it is now on a secondary replica. The above methods that look at the primary replica take one look and don't update. Keep in mind, this is just a different way to achieve very similar results and give more granular control, if you need it. Also, the reason this method wasn't discussed when this question was asked is because Microsoft didn't release this function until after SQL 2014 was released. Below are some samples of how this function can be used:

   IF master.dbo.fn_hadr_database_is_primary_replica('Admin') = 1
    BEGIN 
        -- do whatever you were going to do in the Primary:
        PRINT 'Doing stuff in the Primary Replica';
    END
ELSE 
    BEGIN 
        -- we're not in the Primary - exit gracefully:
        PRINT 'This is not the primary replica - exiting with success';
    END

If you want to use this for user database maintenance, this is what I use:

/*Below evaluates all user databases in the instance and gives stubs to do work; must change to get anything other than print statements*/
declare @dbname varchar(1000)
declare @sql nvarchar(4000)

declare AllUserDatabases cursor for
    select [name] from master.sys.databases
    where database_id > 4 --this excludes all sysdbs; if all but tempdb is desired, change to <> 2
    and [state] = 0

open AllUserDatabases
fetch AllUserDatabases into @dbname

while (@@FETCH_STATUS = 0)
    begin
    --PRINT @dbname
        set @sql = '
            IF master.sys.fn_hadr_is_primary_replica(''' + @dbname + ''') = 1
                BEGIN 
                    -- do whatever you are going to do in the Primary:
                    PRINT ''Doing stuff in the Primary Replica''
                END
            ELSE 
                BEGIN 
                    -- not in the Primary - exit gracefully:
                    PRINT ''This is not the primary replica - exiting with success''
                END             
        '
        exec sp_executesql @sql
        fetch AllUserDatabases into @dbname
    end
close AllUserDatabases
deallocate AllUserDatabases

I hope this is a helpful tip!

I use this:

if (select primary_replica from sys.dm_hadr_availability_group_states) = @@SERVERNAME begin
... paste your t-sql here ...

end

protected by Community Oct 27 at 13:51

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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