Click here to Skip to main content
13,803,326 members
Click here to Skip to main content
Add your own
alternative version

Stats

22.4K views
1.8K downloads
37 bookmarked
Posted 1 May 2016
Licenced CPOL

Simple Dynamic Scheduler

, 23 May 2016
Rate this:
Please Sign up or sign in to vote.
Simple, open-source, fully customizable, lightweight SQL Server scheduler

Introduction

Simple Dynamic Scheduler is alternative scheduling utility which uses SQL Server to store all configurations and logic. 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 - Adoptable 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, 3rd party tolls, etc. But, consider 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). Following image shows columns in t_SimpleDynamicScheduler table that need to be populated depending on 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 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 next example we will create recurring monthly scheduler which will kick off on 12th 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 2016ending 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. 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:

  • @pScheduleId - ID of a schedule (ID column from configuration table)
  • @pLastRun - 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 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 it's usage in defined period of time.

Let's take 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 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).

Image below shows results given by the simulator:

Summary

At this point you should be familiar 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

Apr 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Miljan Radovic
Australia Australia
Business Intelligence Professional passionate about turning data into meaningful information and helping business grow and make better "data-driven" decisions.

You may also be interested in...

Comments and Discussions

 
QuestionInfinite loop with Intraday schedule Pin
Member 126777379-Aug-16 12:13
memberMember 126777379-Aug-16 12:13 
AnswerRe: Infinite loop with Intraday schedule Pin
Miljan Radovic10-Aug-16 3:01
memberMiljan Radovic10-Aug-16 3:01 
QuestionPurpose Pin
Keradinides11-May-16 1:21
professionalKeradinides11-May-16 1:21 
AnswerRe: Purpose Pin
Miljan Radovic11-May-16 4:25
memberMiljan Radovic11-May-16 4:25 
QuestionStill don't get it Pin
Member 111414275-May-16 13:07
memberMember 111414275-May-16 13:07 
AnswerRe: Still don't get it Pin
Miljan Radovic5-May-16 14:40
memberMiljan Radovic5-May-16 14:40 
GeneralFiles seem to be missing. Pin
Emmanouil Gimissis MSc ICSD5-May-16 3:35
professionalEmmanouil Gimissis MSc ICSD5-May-16 3:35 
GeneralRe: Files seem to be missing. Pin
Miljan Radovic5-May-16 4:26
memberMiljan Radovic5-May-16 4:26 
GeneralRe: Files seem to be missing. Pin
Emmanouil Gimissis MSc ICSD5-May-16 4:29
professionalEmmanouil Gimissis MSc ICSD5-May-16 4:29 
GeneralRe: Files seem to be missing. Pin
Miljan Radovic5-May-16 4:38
memberMiljan Radovic5-May-16 4:38 
QuestionIs there no GUI interface? Pin
Dougie the P3-May-16 14:44
professionalDougie the P3-May-16 14:44 
AnswerRe: Is there no GUI interface? Pin
Miljan Radovic3-May-16 16:56
memberMiljan Radovic3-May-16 16:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05 | 2.8.181215.1 | Last Updated 24 May 2016
Article Copyright 2016 by Miljan Radovic
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid