Click here to Skip to main content
11,720,321 members (80,192 online)
Click here to Skip to main content

SQL Agent: A Job Scheduler Framework

, 8 May 2008 GPL3 936.4K 10.2K 298
Rate this:
Please Sign up or sign in to vote.
An easy-to-use .NET solution for Job Scheduling with pluggable actions that runs on a Windows Service. Back up your SQL Server Express and much more!

Introduction

For my last project, I've been using the latest free version of SQL Server, called SQL Server Express Edition, available here. I immediately noticed that the database scheduler was removed since the previous version. I found it really interesting creating my own SQL Agent. In this article, I'll show you how to create a generic scheduler that runs on a Windows Service. The scheduler will be able to accept pluggable actions that we can develop, later implementing a custom interface.

Background

This article is about Jobs Scheduling. You're required to have basic knowledge of how to run and install a Windows Service that uses Microsoft .NET Framework v2.0. A good knowledge of OOP, Interfaces and Reflection is also required in order to understand pluggable actions.

Install It!

The solution provided with this article is composed of four projects:

  • SQLAgent, a Windows Service that wraps the scheduler
  • SQLAgentUI, a Windows Application to control the service from the System Tray
  • EventScheduler, a Class Library containing all the classes composing the Scheduler and the forms to configure it
  • EventSchedulerAction, a Class Library of pluggable actions for the Scheduler jobs

You can install the service using the attached setup or, alternatively, using the command line as shown below:

SQLAgent.exe -i

Run It!

Start the execution of SQLAgentUI and press the button Run beside the label SQL Server Agent. If you do have problems running the system tray application, please go back to the service installation process. Press the button Configure SQL Server Agent to access the configuration page. The following page is shown:

You're now ready to configure your jobs!

Using the Code

The core library of the project is EventScheduler. In fact, the event scheduler allows us to schedule our jobs, configure them with a seamless interface and store the settings through an XML file. All these features are one step away from you. We only need to implement the IActionable interface defined by the library. The following is the implementation of the SQL Backup Job.

Imports Microsoft.SqlServer.Management
Imports EventScheduler
Public Class MSSQLBackup
Implements IActionable

#Region "Implements IActionable"
    Protected Shared DefaultParameters() As ActionParameter = _
        {New ActionParameter("Server", _
        ".\SQLExpress"), _
        New ActionParameter("Database", ""), _
        New ActionParameter("Backup Folder", _
        "C:\Backup\")}

    Public ReadOnly Property ActionParameters() As _
        EventScheduler.ActionParameter() _
        Implements IActionable.ActionParameters
        Get
            Return MSSQLBackup.DefaultParameters.Clone
        End Get
    End Property

    Public Sub ActionRun(ByVal s As EventScheduler.Schedule) _
        Implements IActionable.ActionRun
        Dim oParameters() As ActionParameter = s.GetActionParameters
        BackupDatabase(oParameters(0).Value, _
            oParameters(1).Value, _
            oParameters(2).Value)
    End Sub

    Public ReadOnly Property ActionText() As String _
        Implements IActionable.ActionText
        Get
            Return "SQL Server Backup"
        End Get
    End Property
#End Region
 
#Region "Private Methods"
    Protected Sub BackupDatabase(ByVal ServerName As String, _
        ByVal DatabaseName As String, _
        ByVal BackupFolder As String)
        Try
            Dim srv As New Smo.Server(ServerName)
            Dim bkp As New Smo.Backup
            bkp.Action = Smo.BackupActionType.Database
            bkp.Database = DatabaseName

            Dim filePath As String = BackupFolder
            filePath = System.IO.Path.Combine(BackupFolder, _
                DatabaseName & "-" & Now.Year() & _
                "-" & Now.Month.ToString("00") & _
                "-" & Now.Day.ToString("00") & _
                "-" & Now.Hour.ToString("00") & _
                "-" & Now.Minute.ToString("00") & _
                "-" & Now.Second.ToString("00") & _
                ".bak")

            bkp.Devices.AddDevice(filePath, Smo.DeviceType.File)
            bkp.SqlBackup(srv)

            Catch ex As Exception
        End Try
    End Sub
#End Region
 
End Class

The following is a screenshot of the Jobs Scheduler settings page:

Please note that the Event Scheduler retrieves the data necessary to load the action list using the IActionable interface, as well as the parameters list. The action settings interface will look as follows:

The XML file will have the following aspect:

<?xml version="1.0"?>
    <ArrayOfSchedule xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <Schedule xsi:type="DailySchedule" Name="Daily Backup" 
            Active="false" Interval="0">
            <StartTime>2006-08-30T12:45:30.15625+02:00</StartTime>
            <NextInvokeTime>2006-08-31T12:45:30.15625+02:00
            </NextInvokeTime>
            <LastInvokeTime>2006-08-30T12:45:40.84375+02:00
            </LastInvokeTime>
            <Action>0</Action>
            <ActionParameters>
                <ActionParameter>
                    <Name>Server</Name>
                    <Value xsi:type="xsd:string">.\SQLExpress
                    </Value>
                    <Type>NormalActionParameter</Type>
                </ActionParameter>
                <ActionParameter>
                    <Name>Database</Name>
                    <Value xsi:type="xsd:string">MyDatabase</Value>
                    <Type>NormalActionParameter</Type>
                </ActionParameter>
                <ActionParameter>
                    <Name>Backup Folder</Name>
                    <Value xsi:type="xsd:string">C:\Backup\</Value>
                    <Type>NormalActionParameter</Type>
                </ActionParameter>
            </ActionParameters>
            <WeekDays>
                <boolean>false</boolean>
                <boolean>true</boolean>
                <boolean>true</boolean>
                <boolean>true</boolean>
                <boolean>true</boolean>
                <boolean>true</boolean>
                <boolean>false</boolean>
            </WeekDays>
            <BeginValidTime>2006-08-30T00:00:00+02:00</BeginValidTime>
            <EndValidTime>2006-08-30T00:00:00+02:00</EndValidTime>
        </Schedule>
    </ArrayOfSchedule>

Configure It!

SQLAgent and SQLAgentUI are configured to run with the default SQL Server Express instance. Anyway, it's possible to configure it for named instances of SQL Server. The configuration mainly stands on two files:

  • SQLAgent.exe.config
  • SQLAgentUI.exe.config

Extend It!

The pluggable actions for the Event Scheduler actually define a framework for our Windows Services. This makes the application really extensible! I've included in this release:

  • SQL Server Database Backup using SMO
  • SQL Server Database Shrink using SMO
  • Execution of an SQL command
  • Send Email (when used as a PostAction, a [RESULTSET]tag can be included into the message text)
  • Send SMS using Esendex Web Service (when used as a PostAction, a [RESULTSET]tag can be included into the message text)

Please contribute to this project by sending your actions!!!

Final Notes

I hope that you find this article useful. If you find this article stupid, annoying or incorrect, express this fact by rating the article as you see fit. In the end, you're very welcome at any moment to freely contribute to this project by suggesting improvements, or by submitting code or other materials.

Credits

  • Thanks to Gonzalo Brusella for the reflection implementation
  • Thanks to Lukasz Swiatkowski for the cool glass button
  • Thanks to Nick Wälti for his beautiful gradient panel
  • Thanks to Sriram Chitturi for his article on events scheduling

References

History

  • 30th August, 2006 – First submission
  • 6th April 2007, – Project updated
  • 10th August, 2007 – Project updated
  • 4th September, 2007 - Project updated
  • 18th September, 2007 - Project updated
  • 6th May, 2008 - Project updated

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

Share

About the Author

Danilo Corallo
Software Developer (Senior)
Italy Italy
I am 35 years old and I've been working with C++, Visual Basic .NET, C# and ASP.NET. I have a large experience in Industrial Automation solutions, but I've worked also as Web developer and DBA. I like to share knowledge and projects with other people.

You may also be interested in...

Comments and Discussions

 
GeneralTool for scheduling sql-scripts, backups and more Pin
DaoCode1-Apr-15 12:39
memberDaoCode1-Apr-15 12:39 
QuestionSQL Server 2014 Pin
Member 1148140525-Feb-15 22:27
memberMember 1148140525-Feb-15 22:27 
AnswerRe: SQL Server 2014 Pin
Danilo Corallo22-Mar-15 11:11
memberDanilo Corallo22-Mar-15 11:11 
QuestionRe: SQL Server 2014 Pin
DizzyX24-Mar-15 22:38
memberDizzyX24-Mar-15 22:38 
AnswerRe: SQL Server 2014 Pin
DaoCode1-Apr-15 12:41
memberDaoCode1-Apr-15 12:41 
GeneralRe: SQL Server 2014 Pin
DizzyX2-Apr-15 1:39
memberDizzyX2-Apr-15 1:39 
Questionssrs Pin
Member 1096497028-Jul-14 19:35
memberMember 1096497028-Jul-14 19:35 
GeneralMy vote of 5 Pin
neofitDA14-Mar-14 6:45
memberneofitDA14-Mar-14 6:45 
QuestionVS 2010 Pin
Alberto Chavez12-Jun-13 6:41
memberAlberto Chavez12-Jun-13 6:41 
GeneralMy vote of 5 Pin
pradiprenushe26-Aug-12 23:31
memberpradiprenushe26-Aug-12 23:31 
SuggestionRecommend Vale Software's Express Agent ($79) Pin
jroughgarden23-Apr-12 12:05
memberjroughgarden23-Apr-12 12:05 
QuestionEmail settings Pin
Lupdaloo4-Apr-12 21:16
memberLupdaloo4-Apr-12 21:16 
GeneralMy vote of 5 Pin
OnAClearDiskYouCanSeekForever6-Dec-11 9:52
memberOnAClearDiskYouCanSeekForever6-Dec-11 9:52 
Questionwhat is this " Dna2.Forms.ColorWithAlpha" Pin
Nadi3226-Nov-11 2:04
memberNadi3226-Nov-11 2:04 
AnswerRe: what is this " Dna2.Forms.ColorWithAlpha" Pin
Aman Thakur22-Mar-15 10:52
memberAman Thakur22-Mar-15 10:52 
GeneralRe: what is this " Dna2.Forms.ColorWithAlpha" Pin
Danilo Corallo22-Mar-15 11:08
memberDanilo Corallo22-Mar-15 11:08 
GeneralRe: what is this " Dna2.Forms.ColorWithAlpha" Pin
Aman Thakur22-Mar-15 11:32
memberAman Thakur22-Mar-15 11:32 
GeneralRe: what is this " Dna2.Forms.ColorWithAlpha" Pin
Danilo Corallo22-Mar-15 11:48
memberDanilo Corallo22-Mar-15 11:48 
GeneralRe: what is this " Dna2.Forms.ColorWithAlpha" Pin
Aman Thakur22-Mar-15 11:53
memberAman Thakur22-Mar-15 11:53 
QuestionAgent not working in another user. Pin
SamRST15-Nov-11 2:59
memberSamRST15-Nov-11 2:59 
QuestionI can´t start the service ... Pin
emmanuelJuarez8-Apr-11 13:42
memberemmanuelJuarez8-Apr-11 13:42 
GeneralError: Backup Failed - Failed to connect to server Pin
kiki1322-Feb-11 6:40
memberkiki1322-Feb-11 6:40 
GeneralRe: Error: Backup Failed - Failed to connect to server Pin
Steve Pritchard5-Apr-11 14:38
memberSteve Pritchard5-Apr-11 14:38 
GeneralThe .Net SqlClient Data Provider has received a severity 14, state 1 error number 18456; Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Pin
Member 769222922-Feb-11 3:07
memberMember 769222922-Feb-11 3:07 
Generalsource with no dna Pin
Member 24400167-Feb-11 7:09
memberMember 24400167-Feb-11 7:09 
Generalcannot open mssql$sqlexpress service on computer Pin
thishanc31-Aug-10 18:39
memberthishanc31-Aug-10 18:39 
GeneralRe: cannot open mssql$sqlexpress service on computer Pin
kevin hadfield17-Sep-12 10:17
memberkevin hadfield17-Sep-12 10:17 
GeneralMy vote of 5 Pin
sharadyaaaaa25-Jul-10 1:58
membersharadyaaaaa25-Jul-10 1:58 
GeneralSMTP SSL [modified] Pin
ipi_35921-Jul-10 4:42
memberipi_35921-Jul-10 4:42 
GeneralCannot open MSSQL$SQLEXPRESS service on Computer Pin
Philip Horan11-Mar-10 2:05
memberPhilip Horan11-Mar-10 2:05 
GeneralRe: Cannot open MSSQL$SQLEXPRESS service on Computer Pin
Philip Horan11-Mar-10 2:10
memberPhilip Horan11-Mar-10 2:10 
GeneralRe: Cannot open MSSQL$SQLEXPRESS service on Computer Pin
kevin hadfield17-Sep-12 10:16
memberkevin hadfield17-Sep-12 10:16 
GeneralSchedule daily backup for sql server 2005 datbase using sql agent Pin
kirancherupally8-Mar-10 18:20
memberkirancherupally8-Mar-10 18:20 
GeneralGot this working but only on a computer running Windows Authentication Pin
ccastanedo23-Feb-10 15:06
memberccastanedo23-Feb-10 15:06 
GeneralUnable to get the scheduled job run Pin
vihsuna19-Feb-10 6:58
membervihsuna19-Feb-10 6:58 
GeneralMultiple Actions for a Schedule Pin
LT30011-Jan-10 4:00
memberLT30011-Jan-10 4:00 
GeneralDoesn't work under Vista 64x Pin
Kehagiem28-Dec-09 5:25
memberKehagiem28-Dec-09 5:25 
GeneralDoesn't appear to work with SQL Server Express 2008 Pin
KazMaxLtd25-Dec-09 12:23
memberKazMaxLtd25-Dec-09 12:23 
GeneralRe: Doesn't appear to work with SQL Server Express 2008 Pin
Danilo Corallo25-Dec-09 12:47
memberDanilo Corallo25-Dec-09 12:47 
GeneralRe: Doesn't appear to work with SQL Server Express 2008 Pin
KazMaxLtd25-Dec-09 14:35
memberKazMaxLtd25-Dec-09 14:35 
QuestionRe: Doesn't appear to work with SQL Server Express 2008 Pin
fierikit15-Jan-10 0:40
memberfierikit15-Jan-10 0:40 
AnswerRe: Doesn't appear to work with SQL Server Express 2008 Pin
AsafMeir10-Feb-10 22:27
memberAsafMeir10-Feb-10 22:27 
GeneralFailing when connecting to db Pin
Lorenzo Consegni10-Dec-09 22:57
memberLorenzo Consegni10-Dec-09 22:57 
GeneralRe: Failing when connecting to db Pin
Kikoz6811-Dec-09 16:07
memberKikoz6811-Dec-09 16:07 
GeneralRe: Failing when connecting to db Pin
Lorenzo Consegni16-Dec-09 5:28
memberLorenzo Consegni16-Dec-09 5:28 
QuestionHow to get results Pin
Nitin Sawant7-Dec-09 22:14
memberNitin Sawant7-Dec-09 22:14 
AnswerRe: How to get results Pin
Danilo Corallo11-Dec-09 22:04
memberDanilo Corallo11-Dec-09 22:04 
GeneralMy vote of 1 Pin
plume27-Aug-09 1:49
memberplume27-Aug-09 1:49 
RantRe: My vote of 1 Pin
Member 27934304-Sep-09 13:01
memberMember 27934304-Sep-09 13:01 
AnswerRe: My vote of 1 Pin
Steve Pritchard29-Nov-09 6:11
memberSteve Pritchard29-Nov-09 6:11 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150901.1 | Last Updated 8 May 2008
Article Copyright 2006 by Danilo Corallo
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid