Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 12 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.

share|improve this answer

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.

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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