SQLCLR trigger vs Windows service. When is it appropriate to use SQLCLR?

Question

We have a .NET e-commerce application with a SQL Server 2005 back-end. A new order requires certain "post-processing". These tasks include sending emails, creating files, uploading files to an FTP server, and performing CRUD operations against a WCF data service. The code to perform all these tasks is already in place as several .NET class libraries.

The debate that my team is having is where to place this code. I've written a simple Windows service that polls the db at regular intervals and upon detecting new transactions (based on flags) in the db, it does the necessary thing and logs any errors. An alternative that has been proposed is a SQLCLR INSERT trigger that will set off the processing.

I know it's technically possible to do much (all?) of the above tasks in SQLCLR -- I've even found a number of articles explaining how to consume web services from within SQLCLR, so apparently people are doing this. But I am still hesitant. Was SQLCLR ever intended for this kind of thing? And if not, what could be the practical downside? As for the potential benefits of a SQLCLR trigger over a windows service, I can only see one: less db traffic. We expect very light transaction volume initially, so the windows service will generate some "wasteful" traffic. But the service is on the same box as the database, so it doesn't even effect the network, only internal server resources.

Finally, a third possibility would be to use a SQLCLR trigger to save a simple token on the file system, and use FileSystemWatcher in the Windows service (instead of Timer) to perform the tasks as needed.

Please share your thoughts on the trade-offs of these different approaches or suggest a better alternative.

Answer 1

When looking at a similar process here were some things we considered.

1. What happens when a dependant system (mail, ftp) isn't available?
2. How does the recovery of a failure work?
3. If we're in a fail state what happens to new records. Do they try and do the post process even when we know the system is down?
4. What is the skill set of those who are supporting the production system?
5. How is this process monitored?
6. Is the post processing system consuming the resources of the main system while processing? How would do you separate them?
7. Is fail-over supported?

We choose to use a Windows Service because it offered the best experience to those that would be supporting the system (people who would spend more time on serverfault instead of stackoverflow). Since it had an established way to stop and start the service, monitoring, cluster support, easy to split processing to multiple machines, integration with event logging, etc.

Answer 2

There are some things that the SQL CLR was designed for - and this isn't one of them, putting that sort of stuff in an INSERT trigger really would make baby Jesus cry.

One potential solution would be to use Query Notification in order to lighten the load between the service and the database - another route could be taken by allowing the service to be 'pinged' via UDP or something similarly light-weight - meaning that you aren't causing masses to happen on INSERTS (and you are therefore not locking the underlying table for excessive amounts of time).