Express Agent for SQL Server Express: Jobs, Jobs, Jobs, and Mail

UPDATE: My appologies, but with the advent of relatively inexpensive commercial solutions avaiable, I've decided to suspend this project indefinitely. If I do need a solution for myself, I may take it up again. But until then, I would recommend getting a commercial version (http://www.valesoftware.com/products-express-agent.php is one source) or using the Windows Task Manager to run batch files.

UPDATE 2: I no longer "officially" recommend Vale's agent; though I've used the product for well over a year, they were completely non responsive (via phone or email) to a showstopper bug in their product (stopped working after 24 hours when a job was set to run every 5 minutes). My workaround was to have a Windows Task stop then start VAle's SQL Agent service. Also, as a commenter noted, a free version (http://www.lazycoding.com/products.aspx) is out there - I have not used this, however.

I was pretty excited to learn about SQL Server: Express Edition. It is a stripped-down of version of SQL Server that is free to get, free to use, and free to distribute. This is great news if you're in the business of building small- and mid-sized database applications but not in a position to fork over five grand for the full edition.

A free, stripped-down version of SQL Server is nothing new; afterall, MSDE filled this niche for the previous version of SQL Server. One thing that sets SQL Server Express apart is its branding and accessiblity. Not only does Express "feel" like SQL Server, it's easy to install, use, and administer. MSDE did not have these qualities, which kept it out of the reach of many would-be database developers.

The limitations imposed by SQL Server Express do not hinder most small- and mid-sized applications. A single processor and a gigabyte of RAM is enough to run most of these applications and it certainly takes a *lot* of data to fill a database up to four gigabytes. One thing that makes Express a deal-killer is the lack of SQL Agent, which runs scheduled jobs and automates backups. That's important in just about all-sizes of applications.

I'm developing an application that will fill this functionality gap: Express Agent. I was hoping to have this complete before the launch of SQL Server Express, but other priorities prevented this from happening. Express Agent strives to replace and improve upon the SQL Agent that was left out.

Like the SQL Agent, Express Agent runs as a service. However, Express Agent can also be "plugged in" to a hosted web-application as a HttpHandler. This allows Express Agent agent to run as background thread, running jobs and sending email as needed.

The jobs are modeled in a similar fashion to the way SQL Server handles them. A job contains a number of tasks (SQL Scripts) that are run depending on whether the previous task was successful (no errors) or successful (errors). Jobs can also be scheduled on a one-time, idle, start-up, and recurring basis. The recurring schedule is handled much the same way SQL Server handles jobs as well.

Express Agent also adds database-email capability to Express Edition. Though not as complex as SQL Server's implemntation, this should cover just about any emailing you'd need to do from within your stored procedures. The mail feature is used to send success/failure notifications after jobs have been run.

It's difficult for me to show progress, since much of the work I've done is the "behind the scenes" stuff. I'm still working out the UI, HttpHandler, and some other issues, but so far it works great on it's own, so long as jobs are added via the stored procedures. No less, here's a few screen shots from the Jobs Manager UI ...

If this app looks like it may be of interest to you, I'd appreciate your feedback. If you're interested in lending a hand with some of the remaining portions, I'd really appreciate that, too. I plan on offering this completed product for free, but most likely not open source.

13 Comments

  • A good idea, done well.

  • I did something like this in the past, But since I didn't want to reinvent the wheel I used the windows Task scheduler to schedule the job to run by calling my job executable with command line argument giving it what job to run. So all my program had to do is worry about running x job pass or fail and log the results, not on scheduling the job to run or the details relating to that. Also made debugging a great deal easier to do.

  • Very impressive. I look forward to seeing you final version. The missing SQL Agent was my only complaint for SQL Express.

  • Cool. Any chance you'll open source it?



    Also, any chance your make it so it can load .NET DLLs and execute them? Maybe by implementing an "ISqlAgentTask" interface? Then you could ad helper functionality to that most tasks would need, like error logging, etc.

  • If you're still looking for help then i'm happy to offer a limited service.



    We use the SQL Agent within MSDE for some of our solutions at present and so the release of an agent for SQL2005 would help us move forward.



    Strengths are with SQL Server 2000, Visual Basic6, .net 1.1 (c#) and just moved to .net 2.



    I can offer around five hours a week.



    david@imaginsolutions.co.uk

  • Also, if you are still looking for more help I can help some too.



    mitchel.sellers (at) gmail.com

  • I just stumbled upon this "non-blog" while searching for an SQL Agent tool for 2005 Express. Since most of the responses are rather old I was wondering what kind of progress you've made and if your Express Agent was available for use yet?

  • my company actually uses express for some of our smaller clients and this app would be an extraordinary help for our services... right now we are emulating an agent using sqlcmd and batch files but this would be stupendous. id like some info and possibly help out

  • This is a must-have for me with SQL Server Express. I'm glad someone is tackling this. Is the release version ready yet?

    TIA, Kris.

  • The idea of SQL Express Agent is just fantastic.
    You won't believe what kind of problems we have encountered when one of the branches were running out of 2 Gig space allowed by MSDE.
    Once migrated to SQL 2005 Express, all the scheduled jobs stopped - inclyuding internal DB backup!
    I bet stripping off Agent was the great idea by Microsoft - to force you into buying full version of SQL 2005.
    They offer scheduling via Windows Scheduler - sounds like asking you top move from Windows back to DOS!
    But whet dowe schedule? We don't have just a stored proc to call - we have DTS packages (and they do not exist in Express either!)
    So: download DTS runtime?
    What a nightmare of trying to do a simple task that existed in the previous version!

  • Danilo - is there a way to run your SQL Agent using a service name of "SQLSERVERAGENT" versus "SQLAgent"? I have some apps that depend on the service name being "SQLSERVERAGENT". I tried changing the service name for your app in the registry, but then it wouldn't start.

    Thanks.

    -C

  • looking for warehouse work

  • Anybody Know if i can find a version from SQLAGENT for Windows x64 plataform?

    www.codeproject.com/.../SQLAgent.asp

Comments have been disabled for this content.