Posted by: Denny Cherry
Service Broker, SQL
Service Broker is a transaction message queueing system build into Microsoft SQL Server. It provides you with in order, guaranteed single read, message processing that is handled and managed with T/SQL code. This makes it extremely easy to send and process messages either within a single database, or send those messages to a remote database on another server for processing. Messages are sent as XML documents so a message payload can contain a single field of data or a multiple row record set as a single message.
For those familiar with Microsoft Message Queue you will find that Service Broker is very similar to MSMQ but is native to the Microsoft SQL Server.
Server Broker can route messages from database to database, or server to server. Messages can be processed or routed to another server for processing there. Queues can be setup to hold messages for processing by an application or job, or have the messages processed as soon as they arrive by an activation procedure which is simply a procedure which is fired as messages arrive. Activation procedures can be run as a single thread or several threads pulling from the same queue at once.
Look for a future blog posting on configuring and using the service broker to send and process messages.