search

taddison / tsqlScheduler

Create and monitor agent jobs & execution from tables
MIT License
3 stars 0 forks source link

readme

Migrated to DBTrenches/tsqlscheduler

Installation | Notes & Requirements | Managing Tasks | Monitoring | How It Works

tsqlScheduler

When running an Availability Group installation, you may wish to have the Server Agent execute Jobs against your highly available databases. However in the event of failover these Tasks will not exist by default on the replica. Additionally if you modify a Job on the primary replica, keeping parity on each secondary replica requires a separate action. This module automates parity of Server Agent Jobs across all replicas and makes Tasks executed by these Jobs Highly Available alongside any HA database.

Create agent jobs from definitions stored in SQL tables. Currently supports the following features:

This is intended as an administrative tool and as such requires and will schedule jobs to run with sysadmin / sa privileges.

Installation

Availability Group Mode

A config file for your AG is required. This file should be located in the deploy/servers directory with the naming convention agName.json and should be of this form (for an AG with a name of AG1-sample). The deploy script will attempt to auto-create a config file in the event one is not found, although you will need to manually enter some information.

Standalone (Instance) Mode

While the Scheduler is developed for use in an AG environment, it is possible to administer your Server Agent in a single-instance environment as well.

Jump to: Removing the Scheduler

Notes and Requirements

Managing Tasks

Jobs are managed via rows in the scheduler.Task table. Use the UpsertTask proc to create, modify, or delete a task. To retrieve the current values for a task & make incremental modifications, you may use the GetTask proc. You can also maintain a separate repository with configuration files and publish tasks as described here.

Usage of UpsertTask is demonstrated below.

declare 
    @jobName nvarchar(255) =  N'Utility-agDatabase-TaskName',
    @tsql nvarchar(max) =     N'',
    @operator nvarchar(128) = N'Test Operator'; 

exec scheduler.UpsertTask
    @action =            'INSERT', -- or 'UPDATE' or 'DELETE'
    @jobIdentifier =     @jobName, 
    @tsqlCommand =       @tsql, 
    @startTime =         '00:00', 
    @frequencyType =     3, 
    @frequencyInterval = 1, 
    @notifyOperator =    @operator, 
    @isNotifyOnFailure = 0;

You can then either wait for the AutoUpsert job to run, or force creation of the agent job:

exec scheduler.CreateJobFromTask @identifier = 'Utility-agDatabase-TaskName'

After deleting a task (UpsertTask @action='DELETE, @task=...) in order forcibly delete the agent job, either wait for the AutoUpsert job or manually call the RemoveJobFromTask procedure. You are then free to delete the row from the task table.

Column reference

Frequency Type

FrequencyInterval corresponds to the x for frequency types 2-4. It must be 0 if the frequency is set to daily.

The StartTime specified is either the time of day the job will run (if once per day), or the time of day the schedule starts (for any other frequency).

If IsNotifyOnFailure is true (1) then the specified operator will be notified by email every time the job fails.

Monitoring

You can monitor executions via the scheduler.TaskExecution table.

Task configuration history is available in the scheduler.TaskHistory table, or by querying the scheduler.Task table with a temporal query.

You can view currently running tasks by querying the scheduler.CurrentlyExecutingTasks view. The SQL below will show all executing tasks as well as their last runtime & result.

select  te.StartDateTime
        ,datediff(second,te.StartDateTime, getutcdate()) as DurationSeconds
        ,t.Identifier
        ,lastResult.StartDateTime as LastStartTime
        ,datediff(second,lastResult.StartDateTime, lastResult.EndDateTime) as LastDurationSeconds
        ,lastResult.IsError as LastIsError
from    scheduler.CurrentlyExecutingTasks as cet
join    scheduler.GetInstanceId() as id
on      cet.Instanceid = id.Id
join    scheduler.Task as t
on      t.TaskId = cet.TaskId
join    scheduler.TaskExecution as te
on      te.ExecutionId = cet.ExecutionId
outer apply (
    select top 1 *
    from scheduler.TaskExecution as teh
    where teh.TaskId = t.TaskId
    and teh.ExecutionId <> te.ExecutionId
    order by ExecutionId desc
) as lastResult

How it works

The Task table holds one row for each task that should be executed in the context of that database. When an agent job is created from this task a job is created as a wrapper around the scheduler.ExecuteTask stored procedure. This procedure uses the metadata from the Task table to execute the TSQLCommand with sp_executesql.

Before the task is executed the Id of the instance, task, and execution are stored in the context_info object, which allows the task to be tracked via the scheduler.CurrentlyExecutingTasks view.

The auto-upsert logic uses the temporal table field SysStartTime on the Task table, and the agent job's last modified date, to determine which jobs require modification.

Tracking Replica Role (AG Mode)

The RecordReplicaStatus job is created on installation and runs to periodically persist the current status of each node to ReplicaStatus. This table is then queried by ExecuteTask to see if the task should execute. This is done to minimise blocking caused by many concurrent tasks querying the relevant AG DMVs.

Server Time

The server needs to be in the UTC time zone for the solution to work correctly. This is due to the comparison of sysjobs.date_modified to Task.SysStartTime in the UpsertJobsForAllTasks procedure. SysStartTime is always recorded in UTC, whereas date_modified uses the server time. If the server is not in UTC then there may be delays in job changes propagating to the agent job, or jobs may be recreated needlessly (depending on whether the server is ahead of or behind UTC).

Code Style