SQL Server Service Broker was introduced in Microsoft SQL Server 2005 and this article suggests several uses, the most significant of which is probably the ability to do asynchronous processing. Service Broker might be seen as the database equivalent of Microsoft Message Queuing.
In this scenario, a synchronous process can put a message on a queue and then complete immediately (i.e. return control to the user). A separate process can then take this message off the queue and perform some processing based on it – this processing is effectively done “behind the scenes” as far as the user is concerned. Typically, it might be some relatively time-consuming work which isn’t on the user’s critical path.
As with all asynchronous processes, the problem of how to report errors is a tricky one, given that the user might have gone home for the evening by the time they arise. You might imagine keeping a log of such errors and generating notifications, all of which could also be done from SQL, using Database Mail for example.
Getting started with Service Broker
Service Broker’s implementation may seem unnecessarily complicated, involving not only queues but also things called services, contracts and message types. These have been introduced to allow the functionality to be used in as many weird and wonderful ways as realistically possible but they do present a bit of a steep learning curve. So here’s a very simple set of scripts to get you going and show how it works.
Begin by running the first script from any existing database on a SQL Server, version 2005 or later. It creates a new database called ServiceBrokerDemo and makes in it a Queue, Service, Conversation, Message Type and a couple of example stored procedures.
Once that has completed, run the second script:
This puts some messages on the queue by calling the Proc_Queue_Send_Message stored procedure (which completes quickly). It then calls the Proc_Queue_Process_Messages stored procedure; this reads messages off the queue and processes them, putting the results in the T_QueueProcessingResult table. Proc_Queue_Process_Messages mimics doing “hard work” via a WaitFor Delay '00:00:05‘ statement.
An alternative way of working is implemented in the third script, which should then be run:
Now, Proc_Queue_Process_Messages will be called automatically whenever a message is put on the queue – i.e. there’s no need to explicitly call it.
Finally, run the fourth script:
This will again put some messages on the queue. Once it has completed (quickly) the processing will have been started and can be monitored by re-running the final Select * From dbo.T_QueueProcessingResult With (NoLock) statement. Every ~5s, a new row should appear (5 in total).
An aside about Conversations
As in real life, striking up a “conversation” can be a time-consuming process so these are generally cached outside of the Service Broker framework. The TSys_ConversationHandle table is used to do this.