Windows Service or SQL Job?

Question

I have an archiving process that basically deletes archived records after a set number of days. Is it better to write a scheduled SQL job or a windows service to accomplish the deletion? The database is mssql2005.

Answer 1

It depends on what you want to accomplish. Do you want to store the deleted archives somewhere? Log the changes? An SQL Job should perform better since it is run directly in the database, but it is easier to give a service acces to resources outside the database. So it depends on what you want to do,,,

Answer 2

I would think a scheduled SQL job would be a safer solution since if the database is migrated to a new machine, someone doing the migration might forget that there is a windows service involved and forget to start/install it on the new server.

Answer 3

In the past we've had a number of SQL Jobs run. Recently, however, we've been moving to calling those processes from .Net code as a client application run from a windows schedule task, for two reasons:

1. It's easier to implement features like logging this way.
2. We have other batch jobs that don't run in the database, and therefore must be in windows scheduled tasks. This way all the batch jobs of any type will be listed in one place.

Answer 4

Is this for you/in house, or is this part of a product that you distribute.

If in house, I'd say the SQL job. That's just another service too.

If it's part of a product that you distribute, I would consider how the installation and support will be.

Answer 5

Please note that regardless of how you do it, for this task you do not want a service. Services run all day, and will consume a bit of the server's ram all day.

In this, you have a task you need to run, and run once a day, every day. As such, you'd either want a job in SQL Server or as Joel described an application (console or winforms) that was setup on a schedule to execute and then unload from the server's memory space.

Answer 6

To follow on Corey's point, if this is externally distributed, will you need to support SQL Express? If not, I'd go with the SQL job directly. Otherwise, you'll have to get more creative as SQL Express does not have the SQL Agent that comes with the full versions of SQL 2005 (as well as MSDE). Without the SQL Agent, you'll need another way to automatically fire the job. It could be a windows service, a scheduled task (calling a .NET app, powershell script, VBscript, etc.), or you could try to implement some trigger in SQL Server directly.