Scheduling Jobs w/o Agent

I have some customers that can afford nothing but SQL express. So I thought no problem, they won't exceed the filegroup limit anyway.

Scheduling jobs is the only problem. What would you do?

• I could build a simple scheduling agent using the service broker ( but naaah, really?)
• Use Scheduling Tasks and SQLCMD ( embarrassing )
• Pay for, or use a freeware, third party SQL Scheduling Agent ( the easy way )

Many of you ought to have faced this problem before I thought...

I found an interesting project on codeproject. http://www.codeproject.com/KB/database/SQLAgent.aspx
I'm going to examine the code and I might even convert it to C#.

another way is to do what I did for one customer to have automatic backups...

write a windows service in .net that runs a backup command each night just after midnight.

from a service you can run whenever you need to and do all kinds of background work. not just with sql server.

with .net installed there is a small .exe in the framework tools that will install the service. just zip up the exe and dlls of your code and put them on the target machine and run the installutil.exe and then start the service from control panel.

.Net / Visual Studio have a set of classes and a template that hooks up your code to windows services.

you just have to do the code that waits/polls/ loops  for the events / times of day etc....
I use the .net background worker to put my working service on a thread.

then I have the OnStart and OnStop events tell the background worker to start / stop.

I use this with several things I run for different clients and It works very well for them.

Go with task Scheduler & SQLCMD. Simple is beautiful and it makes the end result easy to tweak for a sys admin.

Matthew van Eerde said:

AndyC said:

*snip*

+1.

The SQLCMD stuff is very clean however if you start wanting to do other actions like FTP the db or change the name of the database each time it backs up (to append a date for example) it becomes a bit more involved (although obviously possible) and easier to do in .net.

I found a snipit of code that used the SQL DMO components to backup a database. I added a little bit on to read the database list from an xml file. Maybe you could do something with the DMO stuff?

Example:
http://www.devcafe.co.uk/downloads/sqlBackup.zip