Simple Dynamic Scheduler

• Download Create_table_t_SimpleDynamicScheduler - 1.1 KB
• Download Create_function_f_SimpleDynamicScheduler_CheckRun - 2.6 KB
• Download Create_function_f_SimpleDynamicScheduler_Simulate - 685 B

Introduction

Simple Dynamic Scheduler is an alternative scheduling utility which uses SQL Server to store all configurations and logic. The main characteristics of Simple Dynamic Scheduler are:

• Simple - Easy to manage (once you finish reading this page)
• Dynamic - Not static
• Purely T-SQL - says it all
• Open source - Adaptable code to suit your business needs
• Light-weight - Provides only decision mechanism (Run or Hold) without actually executing anything

There are many schedulers today that can be used out-of-the-box, such as Windows Tasks, SQL Server Job Scheduler, 3^rd party tolls, etc. But, consider the following situations:

• You are unable to use any of the out-of-the-box schedulers.
• You can use out-of-the-box schedulers, but it takes time and involves a lot of internal processes (waiting for OPS or DBA) to add/modify/remove/disable/enable schedulers.
• It is not practical to create hundreds or thousands of scheduling tasks / jobs.
• Your scheduler should change, depending on certain business rules.

If any of the situations presented above sound familiar, then Simple Dynamic Scheduler might be just the right solution for you.

Background

Simple Dynamic Scheduler is built using SQL Server Scheduling Job as a reference. It provides you the ability to define:

• Name of a scheduler
• Schedule type (one time or recurring)
• Schedule frequency (daily, weekly, monthly)
• Recurrence (scheduler recurs every X days, weeks, months)
• Days of week to run (for weekly schedules)
• Exact date or weekday of the month to run (for monthly schedules)
• Daily occurrence (once or multiple times per day)
• Retention period of a scheduler (start and end date)
• Active / Valid days (days to run schedule)
• If scheduler is active of not (enabled or disabled)

All configuration parameters for all schedulers are stored in the configuration table. Only information that is not stored in the configuration table is last execution time of process which uses certain scheduler, because multiple processes can use same scheduler. Thus, you will need to maintain this information separately for your processes.

Code for Simple Dynamic Scheduler is pretty straightforward and consists of:

• Table which contains schedule configurations (t_SimpleDynamicScheduler)
• Scalar function which determines if certain scheduler should run or not (f_SimpleDynamicScheduler_CheckRun)
• Table-value function which is used to simulate schedule run sequence (f_SimpleDynamicScheduler_Simulate)

Remember to follow this exact order of execution when creating database objects.

Types of Schedulers

Following types of schedulers can be created using Simple Dynamic Scheduler:

• One time
• Recurring
  • Daily
  • Weekly
  • Monthly
    • On the exact date
    • On the exact weekday

Additionally, all recurring schedulers may have intraday schedules (more than one schedule run per day). The following image shows columns in t_SimpleDynamicScheduler table that need to be populated depending on the type of the scheduler we want to create:

Green fields are mandatory, yellow are optional and red are ignored. Using examples, we will show you how to create each of different types of schedulers.

Creating the Scheduler

As mentioned earlier, there are 5 different types of schedulers that can be defined using Simple Dynamic Scheduler. Using fictional scenarios, we will explain how to create each type of scheduler through an example. In scenario, we will underline important words and mark them with the subscript numbers. Later, we will use these subscript numbers to explain how information from scenario should be translated into the configuration table fields.

One-Time Scheduler

Let's assume we want to create one-time₁ scheduler which will kick-off on 05 Apr 2016₂ at 10:30pm₃. Following fields should be initialized:

• Name = My one-time scheduler (can be anything)
• ¹Type = O
• ³TimeStart = 22:30:00
• ²StartDate = 2016-04-05
• Enabled = True

Recurring, Daily Scheduler

If we want to create recurring₁ daily₂ scheduler which kicks of every three days₃ once per day₄ at 9:00am₅ starting from 20 Apr 2016₆, ending on 31 May 2016₇, we would initialize the following fields:

• Name = My daily recurring scheduler (can be anything)
• ¹Type = R
• ²Frequency = D
• ³RecurseEvery = 3
• ⁴DailyFrequency = O
• ⁵TimeStart = 9:00:00
• ⁶StartDate = 2016-04-20
• ⁷EndDate = 2016-05-31 (inclusive)
• Enabled = True

Recurring, Weekly Scheduler

Let's imagine we want to create recurring₁ weekly₂ scheduler which kicks off every second week₃ on Monday, Thursday and Friday₄ once per day₅ at 2:30pm₆, starting from 15 Mar 2016₇:

• Name = My weekly recurring scheduler (can be anything)
• ¹Type = R
• ²Frequency = W
• ³RecurseEvery = 2
• ⁴DaysOfWeek = Mo-Th-Fr
• ⁵DailyFrequency = O
• ⁶TimeStart = 14:30:00
• ⁷StartDate = 2016-03-15
• Enabled = True

Recurring, Monthly Scheduler on the Exact Date

In the next example, we will create recurring₁ monthly₂ scheduler which will kick off on 12^th day₃ of every third month₄, once per day₅ at 11:00am₆ starting from 10 Feb 2016₇ only on workdays₈:

• Name = My monthly, on the date, recurring scheduler (can be anything)
• ¹Type = R
• ²Frequency = M
• ⁴RecurseEvery = 3
• ³MonthlyOccurrence = D
• ³ExactDateOfMonth = 12
• ⁵DailyFrequency = O
• ⁶TimeStart = 11:00:00
• ⁷StartDate = 2016-02-10
• ⁸ValidDays = Mo-Tu-We-Th-Fr
• Enabled = True

Recurring, Monthly Scheduler on the Exact Weekday

Similarly to previous scheduler, if we want to create recurring₁ monthly₂ scheduler which will kick off every second₃ Sunday₄ of each month₅ once per day₆ at 11:45pm₇ starting from 1 Jan 2016₈ ending on 31 Dec 2016₉:

• Name = My monthly, on the weekday, recurring scheduler (can be anything)
• ¹Type = R
• ²Frequency = M
• ⁵RecurseEvery = 1
• ⁴MonthlyOccurrence = W
• ⁴ExactWeekdayOfMonth = Su
• ³ExactWeekdayOfMonthEvery = 2
• ⁶DailyFrequency = O
• ⁷TimeStart = 23:45:00
• ⁸StartDate = 2016-01-01
• ⁹EndDate = 2016-12-31 (inclusive)
• Enabled = True

Intraday Scheduler (Applies for all Recurring Schedulers)

Intraday Scheduler is used in case we want schedule to run multiple times per day. For example, let's say we want to execute scheduler multiple times per day₁ starting from 9:00am₂ ending at 5:00pm₃ every two₄ hours₅. The following fields should be initialized:

• ¹DailyFrequency = E
• ²TimeStart = 9:00:00
• ⁴OccursEveryValue = 2
• ⁵OccursEveryTimeUnit = H
• ³TimeEnd = 17:00:00

or:

• ¹DailyFrequency = E
• ²TimeStart = 9:00:00
• ⁴OccursEveryValue = 120
• ⁵OccursEveryTimeUnit = M
• ³TimeEnd = 17:00:00

Checking if Schedule Should Run

To determine if certain schedule should run, we will use scalar function called [dbo].[f_SimpleDynamicScheduler_CheckRun]:

SELECT [dbo].[f_SimpleDynamicScheduler_CheckRun] (
     <@pScheduleId, int,>
    ,<@pLastRun, datetime,>
    ,<@pCurrentDate, datetime,>)

Parameters of this function are:

• <a>@pScheduleId</a> - ID of a schedule (ID column from configuration table)
• <a>@pLastRun</a> - Last time when your process / job / activity that uses this scheduler has been run
• @pCurrentDate - Current date and time (usually getdate(), left as a parameter for testing and simulation purposes)

Function returns the following values:

• RUN - process should run
• HOLD - process should not run
• Anything else - there is an error in function. Return value is actual text of an error

Depending on the return result, you should implement logic in your code to:

1. Run necessary code if necessary
2. Update LastRun value of your process if process successfully completed
3. Present an error (if any) to a user

Schedule Simulator

For testing and simulation purposes, we provided you with a function called [dbo].[f_SimpleDynamicScheduler_Simulate]. It gives you the ability to test schedule execution cycles by simulating its usage in defined period of time.

Let's take the following schedule as an example:

• ID = 3
• Type = R
• Frequency = D
• RecurseEvery = 1
• DailyFrequency = E
• TimeStart = 9:00:00
• OccursEveryValue = 120
• OccursEveryTimeUnit = M
• TimeEnd = 16:30:00
• StartDate = 2016-04-25
• EndDate = 2016-05-13
• Enabled = True

To simulate this schedule, we will run the following piece of code:

SELECT *
FROM [dbo].[f_SimpleDynamicScheduler_Simulate] (3, '20160405', '20160520', 'M', 10, null)

Parameters of this function say: for schedule 3, simulate executions in period from 05-Apr-2016 until 20-May-2016 in 10 minute increments. Previous LastRun is not known (NULL).

The image below shows results given by the simulator:

Summary

At this point, you should be familiar with how to create, initialize and use Simple Dynamic Scheduler. If there is still something unclear or I missed something, please feel free to ask and comment and I will make sure those points are better explained.

Enjoy using Simple Dynamic Scheduler!

History

• April 2016 - Initial version
• May 2016 - Added ValidDays parameter which controls what days are "valid" for a schedule. For example, with this new functionality, we can control if schedule can be run on weekend or not by setting valid only days from Monday to Friday.