Windows Service or SQL Job?
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,,,
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.
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:
- It's easier to implement features like logging this way.
- 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.