Running a process that interacts with SQL Server from a Windows Service - alternative to SQL Server agent jobs

Question

I am looking for an alternative to SQL Server Agent jobs - there is a need to control running and scheduling of processes (executables, "jobs") and so I intend to implement a Windows Service that will act as a scheduler for these processes.

These processes are not interactive, so there is no problem starting them from a Windows Service, but they need to work with SQL Server - reading and writing data, executing stored procedures etc.

Commands to windows service ("scheduler") - like "enqueue a job" - would be passed from SQL Server CLR in UDF or directly from a client side web app via WCF (haven't decided yet).

Is this a right way to do this? Are there any access and security issues should I be aware of? I am particulary worried about possible limitations on processes run from a windows service.

Please share your experiences with similar designs.

Also, I consider using SQL Server Service Broker (suggested in similar thread) but I am not sure if it fits my needs.

Thank you! Adam

Answer 1

The right way to achieve a reasonable security level is:

• create a new user that will run the Windows Service (you can create this user in Control Panel)
• create a new login for this user in SQL Server (using Integrated Security)
• give the necessary, but minimum, permissions to this user in SQL Server

To implement a service you must be aware of a few things:

• if you use WCF (or any other service stack), you need a thread for listening the requests and a different thread to run the processes in SQL server. If not, the subsequent requests will probably time out. (You can launch new threads for each task, or implement a queue to avoid overloading the server).
• be extremely careful with error (exception) handling. If an unhandled exception is thrown your service will stop, until someone starts it again
• it's harder to implement the UDF solution, becasuse you need to modify SQL Server security settings to allow an UDF to communicate with the "outer world"
• you should include some kind of logging in your service, so that you can test it and verify it's working correctly.

It's not to hard to make an executable that can be run as console app as well as as a service. If you do so you can run it from the Visual Studio debugger, or stand alone, and log in the console (Console.Write...(...)), to see what's going on.