Sql-server – SQL Server Agent Jobs and Availability Groups

availability-groupssql serversql-server-2012sql-server-agent

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.

Best Answer

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.

Related Solutions

Sql-server – Check if SQL Agent job is being executed manually

Using Kenneth's code, and a few other bits and pieces that I pulled together, I came up with the following code which I think should do the job. If anybody sees any potential problem with this I'd be interested in knowing!

-- Check if this server is the preferred replica for backups.
    If sys.fn_hadr_backup_is_preferred_replica( @DatabaseName ) <> 1
    BEGIN

        -- Check whether the script is running from SQL Agent.  If it is not then raise an error so that the user knows that the backup hasn't completed.
        -- (If the job is run from the agent then there should be an equivalent job running on the preferred replica, so no need to alert).
        declare @AgentAccount varchar(100)
        set @AgentAccount = (select cast(value_data as varchar(100)) as [AccountName]
                                FROM sys.dm_server_registry
                                WHERE registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
                                AND value_name = 'ObjectName'
                            )


        if @AgentAccount = SYSTEM_USER
        begin

            declare @jobSource tinyint
            SELECT  @jobSource = ja.run_requested_source
                    --WHEN 1 THEN 'SOURCE_SCHEDULER'
                    --WHEN 2 THEN 'SOURCE_ALERTER'
                    --WHEN 3 THEN 'SOURCE_BOOT'
                    --WHEN 4 THEN 'SOURCE_USER'  -- This one is manual
                    --WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
                    --ELSE 'UNKNOWN' END
            FROM msdb.dbo.sysjobactivity ja
            WHERE ja.session_id = @@SPID
            AND start_execution_date is not null
            AND stop_execution_date is null;

            if @jobSource = 4   -- Job was run manually.
            begin
                RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
                return 1
            end
            else
            begin
                print 'No backup produced as this server is not the preferred replica for backups.'
                return 0
            end
        end
        else
        begin
            RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
            return 1
        end
    END

Sql-server – AlwaysOn Availability Groups back up schedules

My question is: - Do i have to schedule the jobs at primary replica and then disable them until fail-over occurs and then manually enable jobs or what is the best way to deal with these please?

No, you should just add a check with the new function sys.fn_hadr_backup_is_preferred_replica

if sys.fn_hadr_backup_is_preferred_replica('your db name') <> 1
begin
    print 'This is not preferred backup replica .....'
        -- If this is not the preferred replica, exit (without error).
end
else
begin
    print 'This is the preferred replica .. continue with backup'
        -- If this is the preferred replica, continue to do the backup.
        -- here goes the backup command .....
end

Alternatively, you can use SQL Server Maintenance Solution - by Ola hallengren which supports AlwaysON as well.

Best Answer

Related Solutions

Sql-server – Check if SQL Agent job is being executed manually

Sql-server – AlwaysOn Availability Groups back up schedules

Related Question