you're reading...
SQL Notification Services

An Intro to SQL Server Service Broker


With Service Broker, a feature in Microsoft SQL Server 2005, internal or external processes can send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML). Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server.

To better understand Service Broker, familiarity with the key concepts of queues, dialogs, conversation groups, and activation is helpful. These are discussed briefly in this section.


Service Broker uses queues to provide loose coupling between the message sender and the message receiver. The sender can use the SEND command to put a message in a queue and then continue on with the application, relying on Service Broker to ensure that the message reaches its destination.

Queues permit a lot of scheduling flexibility. For example, the sender can send out multiple messages for multiple receivers to process in parallel. The receivers might not process the messages until long after they were sent, but because incoming messages are queued, the receivers can process them at their own rate and the sender doesn’t have to wait for the receivers to finish before continuing.


Service Broker implements dialogs, which are bidirectional streams of messages between two endpoints. All messages in a dialog are ordered, and dialog messages are always delivered in the order they are sent. The order is maintained across transactions, across input threads, across output threads, and across crashes and restarts. Some message systems ensure message order for the messages sent or received in a single transaction but not across multiple transactions, making Service Broker dialogs unique in this regard.

Each message includes a conversation handle that uniquely identifies the dialog that is associated with it. For example, an order entry application might have dialogs open simultaneously with the shipping application, the inventory application, and the billing application. Because messages from each application have a unique conversation handle, it’s easy to tell which application sent each message.

Conversation Groups

Service Broker provides a way of grouping all the dialogs that are used for a particular task. This method uses conversation groups. In our previous order entry example, all the dialogs associated with processing a particular order would be grouped into a single conversation group. The conversation group is implemented as a conversation group identifier, which is included with all messages in all dialogs contained in the conversation group. When a message is received from any of the dialogs in a conversation group, the conversation group is locked with a lock that is held by the receiving transaction. For the duration of the transaction, only the thread that holds the lock can receive messages from any of the dialogs in the conversation group. This makes our order entry application much easier to write because even though we use many threads for scalability, any particular order is only processed on one thread at a time. This means we don’t have to make our application resilient to problems that are caused by the simultaneous processing of a single order on multiple threads.

One of the most common uses for the conversation group identifier is to label the state that is associated with a particular process. If a process involves many messages over time, it probably doesn’t make sense to keep an instance of the application running through the whole process. For example, the order entry application will scale better if, between messages, any global state that is associated with processing an order is stored in the database and retrieved when the next message associated with that order is received. The conversation group identifier can be used as the primary key in the state tables to enable quick retrieval of the state associated with each message.


You use the activation feature of Service Broker to specify a stored procedure that will handle messages destined for a particular service. When messages arrive for a service, Service Broker checks whether there is a stored procedure running that can process the messages. If there isn’t a running message-processing stored procedure, Service Broker starts one. The stored procedure then processes messages until the queue is empty, after which it terminates. Moreover, if Service Broker determines that messages are arriving faster than the stored procedure can process them, it starts additional instances of the stored procedure until enough are running to keep up with the incoming messages (or until the configured maximum number is reached). This ensures that the right number of resources for processing incoming messages are always available.

Why Use Asynchronous, Queued Messaging?

Queues enable the flexible scheduling of work, which can translate to big improvements in both performance and scalability. To see how, go back to the order entry example. Some parts of an order must be processed before the order can be considered complete, such as the order header, available to promise, and order lines. But other parts realistically don’t have to be processed before the order is committed; for example, billing, shipping, inventory, and so on. If the “delayable” piece of the order can be processed in a guaranteed but asynchronous manner, the core part of the order can be processed faster.

Asynchronous messaging can also provide opportunities for increased parallelism. For example, if you need to check the customer’s credit and check availability for ordered items, starting both processes simultaneously can improve overall response time.

Queuing can also enable systems to distribute processing more evenly, reducing the peak capacity required by a server. For example, a typical incoming order rate might look something like this:


About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.


Comments are closed.

%d bloggers like this: