Partager via

Dealing with Scheduled Tasks in a SQL Server Environment using Database Mirroring

  • Article

Since the introduction of SQL Server Database Mirroring the usage becomes more and more widespread by SAP customers. One of the problems repeatedly pointed out by customers was the handling of scheduled tasks/jobs in a mirrored environment. Hardly any customer where there wouldn’t be any scheduled tasks/jobs which should be executed by SQL Agent. Starting with such essential tasks as executing backups of the Transaction Log, online backups, own monitor scripts to e.g. check on failed update tasks, etc. The usual situation is that in case of an expected or unexpected failover from the Principal to the Mirror server one would need to go into both instances and disable the tasks on the former principal manually in order to not get steady failures. On the former Mirror, now Principal one would have to enable the scheduled tasks/jobs in order to get the backups and other scheduled tasks going. That would require that somebody is around when an unexpected failover happens and that one even notices the failover. Both conditions which not always become true.

In this short article, we’ll show a way how to automate disabling and enabling tasks/jobs based on the fact that a failover occurred. The solution again is a scheduled job via SQL Agent. A few small steps are necessary for this:

1. Naming conventions: Scheduled Jobs which need to run on the instance in case the instance runs the SAP database in the principal role need to be named with a prefix of ‘PRI_’. If there are jobs which need to run when the instance runs the SAP database as a mirror, we want to add the prefix of ‘SEC_’ to each of those jobs

2. Ideally each scheduled job or task more or less exists on both servers with the same names assuming that each and every job needs to run independent of which server runs in Principal role. The only difference of the particular job steps might be eventual server names (addressing the two different server names of Principal and Mirror) or different share/file locations if the servers are not exactly of the same configuration.

3. A toggle job which relies on the naming convention is scheduled on each of the two SQL Server instances and is run every minute. The job will check on the database status of the mirrored database and dependent on the state activate/de-activate jobs. Not too complicated.

Here the rough sketch of what such a toggle job needs to do:

  • Get the state of the mirrored database out of sys.databases
  • If the database is online and
    • There are jobs with the Prefix ‘PRI_’ disabled, then enable the jobs
    • There are jobs with the prefix ‘SEC_’ enabled, then disable the jobs
  • If the database is in restoring state and
    • There are jobs with the prefix ‘PRI_’ enabled then disable those jobs
    • There are jobs with the prefix ‘SEC_’ disabled, then enable those jobs

No doubt, with a job like that one could also trigger other tasks to be done than enabling/disabling the scheduled jobs. One could call other procedures which would copy some data. One could eventually trigger logic which arranges things on the OS level, etc.

A T-SQL coding which does something like this can be seen below. It got developed from our Microsoft SAP basis team for their purposes. Thanks to Eric Holling for creating the script below.

drop proc sp_SAP_auto_job_status_toggle;
go
create procedure sp_SAP_auto_job_status_toggle
(@dbname sysname)
as

--FOR DB MIRRORING ROLE CHANGE, IF DATABASE NOW THE PRIMARY/PRINCIPAL,
--THEN THE FOLLOWING IS DONE:
    --ENABLES AGENT JOBS WITH NAMES LIKE 'PRI%'
    --DISABLES AGENT JOBS WITH NAMES LIKE 'SEC%'
    --ENABLES LOG SHIPPING PRIMARY ROLE ALERTING

--FOR DB MIRRORING ROLE CHANGE, IF DATABASE NOW THE SECONDARY/MIRROR,
--THEN THE FOLLOWING IS DONE:
    --DISABLES AGENT JOBS WITH NAMES LIKE 'PRI%'
    --ENABLES AGENT JOBS WITH NAMES LIKE 'SEC%'
    --DISABLES LOG SHIPPING PRIMARY ROLE ALERTING
    --IF DB MIRRORING NOT ENABLED, THEN ENABLE LOG SHIP SECONDARY ROLE ALERTING

set nocount on;

declare @jobid uniqueidentifier;
declare @dbstate_desc nvarchar(120);

select @dbstate_desc=state_desc from sys.databases where name=@dbname;

-- If the database is online it means it is the Principal

if @dbstate_desc = 'ONLINE'
   begin
    if exists (select * from msdb..sysjobs where name like 'PRI_%' and enabled=0) or
       exists (select * from msdb..sysjobs where name like 'SEC_%' and enabled=1)
    begin
        select getdate();
        print 'Database ONLINE; Need to enable/disable the following jobs due to role change...';
        select name from msdb..sysjobs where name like 'PRI_%' and enabled=0;
        print '';
        --Get all jobs which need to run on Principal but are disabled
        declare job_cursor cursor for
        select job_id from msdb..sysjobs where name like 'PRI_%' and enabled=0;
        open job_cursor;
        print 'Enabling PRI jobs...';
        fetch next from job_cursor into @jobid;
        while @@fetch_status=0
        begin
            exec msdb..sp_update_job @job_id=@jobid , @enabled=1;
            fetch next from job_cursor into @jobid;
        end
        close job_cursor;
        deallocate job_cursor;
        print '';
        --Get SEC jobs
        print 'Need to disable the following SEC jobs:';
        select name from msdb..sysjobs where name like 'SEC_%' and enabled=1
        declare job_cursor cursor for
        select job_id from msdb..sysjobs where name like 'SEC_%' and enabled=1;
        open job_cursor;
        print 'Disabling SEC jobs...';
        fetch next from job_cursor into @jobid;
        while @@fetch_status=0
        begin
            exec msdb..sp_update_job @job_id=@jobid , @enabled=0;
            fetch next from job_cursor into @jobid;
        end
        close job_cursor;
        deallocate job_cursor;
    end
   end
-- Database is in Restoring state, hence it is the mirror
else if @dbstate_desc = 'RESTORING'
   begin
    if exists (select * from msdb..sysjobs where name like 'PRI_%' and enabled=1) or
       exists (select * from msdb..sysjobs where name like 'SEC_%' and enabled=0)
    begin
        select getdate();
        print 'Database is RESTORING; Need to enable/disable jobs due to role change...';
        --Get PRI jobs
        print 'Need to disable the following PRI jobs:';
        select name from msdb..sysjobs where name like 'PRI_%' and enabled=1;
        --Disable PRI jobs
        print 'Disabling PRI jobs...';
        declare job_cursor cursor for
        select job_id from msdb..sysjobs where name like 'PRI_%' and enabled=1;
        open job_cursor;
        fetch next from job_cursor into @jobid;
        while @@fetch_status=0
        begin
            exec msdb..sp_update_job @job_id=@jobid , @enabled=0;
            fetch next from job_cursor into @jobid;
        end
        close job_cursor;
        deallocate job_cursor;
        print '';
        --Get SEC jobs
        print 'Need to enable the following SEC jobs:';
        select name from msdb..sysjobs where name like 'SEC_%' and enabled=0;
        --Enable SEC jobs
        print 'Enabling SEC jobs...';
        declare job_cursor cursor for
        select job_id from msdb..sysjobs where name like 'SEC_%' and enabled=0;
        open job_cursor;
        fetch next from job_cursor into @jobid;
        while @@fetch_status=0
        begin
            exec msdb..sp_update_job @job_id=@jobid , @enabled=1;
            fetch next from job_cursor into @jobid;
        end
        close job_cursor;
        deallocate job_cursor;
    end
   end
else
   begin
    if exists (select * from msdb..sysjobs where (name like 'PRI_%' or name like 'SEC_%') and enabled=1)
    begin
        --INVALID DATABASE STATE, DISABLE ALL PRI & SEC JOBS THAT ARE CURRENTLY ENABLED
        select getdate();
        print 'Database is neither ONLINE nor RESTORING; Disabling all PRI and SEC jobs...';
        --Get enabled PRI & SEC jobs
        print 'Need to disable the following PRI & SEC jobs:';
        select name from msdb..sysjobs where (name like 'PRI_%' or name like 'SEC_%') and enabled=1;
        --Disable PRI & SEC jobs
        print 'Disabling PRI & SEC jobs...';

        declare job_cursor cursor for
        select job_id from msdb..sysjobs where (name like 'PRI_%' or name like 'SEC_%') and enabled=1;
        open job_cursor;
        fetch next from job_cursor into @jobid;
        while @@fetch_status=0
        begin
            exec msdb..sp_update_job @job_id=@jobid , @enabled=0;
            fetch next from job_cursor into @jobid;
        end
        close job_cursor;
        deallocate job_cursor;
        print '';
    end
   end

Comments