Just where did the SQL Agent component of MSDE vanish to when everything changed to the SQL 2005 Express? Having developed and administered on MSDE (a few years ago now) it came as a bit of a shock when the agent suddenly vanished. But we did without, we made do with what we had, stay calm and carry on. SQL Server 2008 Express soon appeared and so did a brief hope of light. The SQL Agent made a reappearance or so it seemed. If you check under Services you will see that it is listed but you won’t be able to start it. Well, maybe you have to run it from the Configuration Manager or SSMS? You can try as many places as you like but none of them will work. Its seems there was a miscommunication or change of heart that meant the SQL Agent component was added back in so that it became visible again but it was left disabled. If you are new to SQL 2008 Express where does that leave you if you want to schedule tasks?
Alternatives to the SQL Agent.
All the SQL Agent did was effectively schedule and run jobs that were SQL code. You can still call code any number of ways, the hard part is getting the code to run at scheduled times or intervals and then reporting on the outcome.
SQL Sentry offers an Event Manager for Tasks Scheduler that currently costs $195 (£120) for each instance that is monitored. If you have 10 Express installs dotted around the company/ customer sites then this can become expensive.
Vale Software has a software release called Express Agent that costs $79 (£50).
There is also a free piece of software called SQLScheduler that currently supports up to 2005.
Personally I use the free option of Windows Scheduler. You just create Scheduled tasks on the machine running Express that calls the code you wish to run using sqlcmd.exe. The code that is running logs all start time, end times and any errors encountered so that I can see if the job has run. The schedule tasks are used to backup the databases, check for index defragmentation, DBCC checks and run some software specific data checks. You don’t even need to spend the time working out your own backup and defrag scripts. SQLfool (Twitter) has a great script that checks for defragmentation in indexes. Over at SQLDBATips you can get another useful script that helps with the backup tasks.
If you have the money to spend and not the time to invest then going for the paid options certainly has its benefits but using the Scheduler with your own (and in this case a few others as well) scripts is also very beneficial.
SQL 2008 Express : http://www.microsoft.com/express/sql/download/
SQL Sentry: http://www.sqlsentry.net/event-manager/windows-task-scheduler-standard.asp
Vale Software Express Agent: http://www.valesoftware.com/products-express-agent.php
SQL Fool Defrag Script: http://sqlfool.com/2009/06/index-defrag-script-v30/
SBADBATips Maint Script: http://www.sqldbatips.com/showcode.asp?ID=26
Microsoft Connect: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=351806&wa=wsignin1.0
More Tips on using Windows Scheduler