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.
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.
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
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!
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>
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:
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:
PostAction, a [RESULTSET]tag can be included into the message text) PostAction, a [RESULTSET]tag can be included into the message text) Please contribute to this project by sending your actions!!!
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.
| You must Sign In to use this message board. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Its almost like reading a foreign language I have never seen before. Though I have quite a bit more VB.Net experience than C#, In the last few months I have learned to love how easy it is to read, and maintain over VB. It could also be that I use VB alot when working on Excel for certain clients, if you have ever done any VBA you know it will make you hate VB forever.
