I’m working on the project that needs some background application servers to execute long-running tasks in order to off load the web server. Architecturally, there are a couple ways that you could do it, but I want to leverage existing infrastructure--that means using MSSQL server. One of the things I want to avoid was polling SQL server for new tasks. SQL server provides a cool technology built right into the database engine called Service Broker. It provides native support for messaging and queuing applications. One of the things you can do with the Service Broker is setup a query notification dependency between an application and an instance of SQL server. This makes it so you can receive notifications from SQL server when a table changes. .NET provides a class called SqlDependency to register to receive these very query notifications. Here is a toy database:
Notice how you have to enable the service broker per database. Here is the class used to register to receive query notifications:
Here is the main class. Run a couple of them. The idea of this little toy program is to dispatch a task to exactly one process. The process gets the change notification and then tries to claim the top item in the queue by trying to insert it into another table. Because SQL enforces primary key uniqueness, exactly one process can successfully insert it. That’s how this toy program decides if it successfully got a queue item.