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.