Posted by: Denny Cherry
If you have used SQL Server 2005 Express Edition (or SQL Server 2008 Express Edition) then you know that the SQL Agent has been removed from the product. I have from time to time found this to be annoying as I want to do things like backup my database on a regular basis, or do other basic things. The normal solution is to either use another SQL Server to do the scheduling, or use the Windows Task Scheduler to do the scheduling.
Using another SQL Server is just annoying, and the Task Scheduler doesn’t have much in the way of reporting capabilities.
Because of this I’ve decided to start up a side project and build a standalone Agent service to take the the place of the missing SQL Server Agent. I’m calling the application the “Standalone SQL Agent” (hopefully no one at Microsoft takes offence at the name).
Now god knows I’m not a .NET programmer, so this may end up taking me a while to finish, but there’s no time to start like the present right? I actually started about a week ago or so, and I’ve got a good amount of the framework done already.
Unlike the Microsoft agent which is installed once per instance, I’m setting this up to run against multiple SQL Server instances at a single time. This isn’t all that much extra work as you simply span a thread for each instance instead of running everything in the main thread. (I’d done some pretty cool multi threaded programming in VB.NET back when I worked at MySpace.com.)
The beauty of the whole thing is that I’m using the Microsoft stored procedures and tables to do all the work so if a vendor product is put on the database instance and it sticks jobs into the tables in the msdb database my agent will pick it up and begin running them right away.
I’ve got the project planned for a few public releases at the moment.
Version 1 will be the basic service only supporting T/SQL and OS Command job step types.
Version 2 will add a UI to manage jobs since SSMS doesn’t allow for job management against a SQL Express database.
Version 3 will add in any of the other job step types which are needed (replication, power shell, etc)
Version 4 will be where I decide if I want to put any sort of alerting into the system (like the alerts monitoring that the SQL Agent has for Workgroup and up).
I’m certainly not going to commit to any dates at this point, I’m way to early in the process. I will commit to keeping everyone up to date via my blog here about how the project is coming.
When I’m ready for larger testing then the instance on my workstation I’ll ask for beta testers to try out the builds and look for any bugs. Like the SQL Server and the normal SQL Agent it’ll log everything to a log file as well as the Windows Application log. While the project is in beta it’ll probably log way more information than is actually needed, but that will be to make any bugs easier to find. At that time I’ll setup some sort of public bug tracking forum to keep track of the problems.
I’m not really sure what I’m going to do with the licensing for this. I’ll probably make it free, or close to it. I’ll worry about that when I get closer to the finished product.
I’m happy to here comments, offers of cash to pay for the thing, someone to send me pizza to keep me going, etc.
Where I’m at
At this point the system downloads all the jobs which are pending execution and loads them into memory. The agent then decides which ones should be run and kicks off threads to get them started. Those threads don’t do anything but close gracefully at the moment, but one thing at a time.
Right now I’m working on the code to keep track of when the job schedules need to run next. Apparently the SQL Agent has to update some values in the msdb database so that it knows when the schedules are suppose to kick off the jobs which are assigned to them again. This logic is fairly complex because of the way that Microsoft is storing some of the data in the database, but I’m hoping to have this part done in the next day or so.
I’m using Visual Basic 2005 to do the development work. Why VB you ask, because I don’t know squat about C#, and I don’t have the patience to learn. Why VB 2005 you ask, that’s what I’ve got installed, and I’m to lazy to download Visual Studio .NET 2008 from MSDN and install it. Will I upgrade it later on? Probably. I’m sure that I’ll be buying a new laptop in the near future (mine is about to hit the two year mark) and when I do I’ll probably upgrade to Visual Studio 2008 at that point.
I’m not sure how often I’ll post about this project. I’ll try and do it as often as makes sense (translation some weeks you may see several posts about it, and some months you may not here anything; I do have a day job after all).