SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Service Broker Alphabet Part 2

By Simon Sabin,

Service Broker Alphabet P-Z

Following on from my first half of the Service Broker alphabet, here is the concluding half.

P is for Protocols

Service Broker is built on a layer of protocols that ensure the reliable deliver of messages. These protocols are specific to Service Broker.

and Poison

Activation procedures will receive messages from a queue. If the processing of a message causes the activation procedure to fail the procedure should rollback and the message go back on the queue. If this occurs 5 times the message is considered to be a poison message and the queue is disabled to prevent corruption occurring. For this reason you should make sure you handle errors rather allowing your procedure to rollback.

Q is for Queue

The Queue is the store for messages for a service.

R is for routing

Routing allows for messages to be sent between Service Broker instances. This can be within a SQL Server instance or between instances. It is essential that if sending messages outside of an instance that the target SQL Server instance has routes defined that allow responses to be sent back to the initiator. If you don't do this, the Initiator will not have any idea that the target service has received the message and so will keep resending the message.


This is the statement used to receive messages from a queue.

and Reliable

Core to the Service Broker architecture is that the delivery of messages is reliable. They are guaranteed to be delivered or an error will be returned. The great thing is that this process is asynchronous so you don't have to worry if the target is available, Service Broker will continue to try and send the message.

S is for Service

This is effectively the address messages are sent to. Imagine emails addresses. You have the user part and the domain part, the domain part identifies where messages are sent between mail systems, in Service Broker land this is like the Service Broker GUID. Once the message arrives at the mail system the user part is used to identify which account to send a message to, this is just like the service name. Further the emails, when delivered are stored in a mailbox, this is just like the Queue. If you want to take it a step further the MX records for the domain define where in the internet world the mail server for the domain exists, this is the same as Service Broker routes.

and Scalability

Service Broker is used for many of the internal systems in SQL Server (Database Mail, Query notifications) it has been designed to scale.

T is Transmission Queue

When messages are sent the transmission_queue is used to stored messages that can't be sent. When developing Service Broker applications this queue will become your friend

and Transactions

Service Broker delivery is reliable. This means that adheres to the transactional rules. If you read a message from a queue whilst in a transaction and then the transaction is rolled back the message will be put back on the queue.

During a transaction the conversation groups that have been read as part of that transaction are locked. This stops any other process from reading other messages in the same conversation.

and Target

The service that receives messages from the initiator is referred to as the Target service

U is for Unsequenced message

Messages for user conversations are sequenced, this means that messages are processed in the order that they are sent. SQL Server uses Service Broker internally and some of these messages are unsequenced, i.e. errors, which means the message is processed in any order.

V is for varbinary(max)

The data type of the content of a message is varbinary(max) this means you can send anything you want. It also means you will likely have to convert the message into something useful when you RECEIVE it.

W is for WAIT FOR

The RECEIVE statement can be combined with the WAITFOR statement to have the RECEIVE statement wait for a message to appear on the queue you are trying to read from. If you don't want to wait indefinitely you can specify a TIMEOUT after which the RECEIVE statement will complete. @@ROWCOUNT can be used to determine if any messages have been received from the queue.

X is for XML

Service Broker is designed to work directly with XML. If you want to ensure messages conform to a standard structure then you need to use XML, you then specify a contract which has as a message type that defines what the structure of the XML should be by the use of an XSD schema.

Y is for Yorkshire Pudding

Nothing to do with Service Broker but it's my favourite aspect of the great British Sunday roast.

Z is for Zone

Due to this being a communication process across servers, across timezones the time Zone for all dates is UTC

Further reading

Blogs & Sites

Rational Guide to SQL Server 2005 Service Broker

Simon is a database architect for Totaljobs Group based in the UK.
He specializes in performance SQL Server systems and recently on search technologies.
To keep up on Simons thoughts on SQL Server and other database related topics, read his blog
You can also read about Simon in a SQLServer Central article by Steve Jones

Total article views: 3554 | Views in the last 30 days: 2
Related Articles

SQL server Service Broker -- Reading same Message by multiple Users in the queue

SQL server Service Broker -- Reading same Message by multiple Users in the queue


Get Your Service Broker Messages in Order. Always.

Learn how you can guarantee the ordering of all messages in a Service Broker queue, regardless of co...


An AlwaysOn Bug with Service Broker Transmission Queue

Paul Brewer talks about an AlwaysOn problem affecting the Service Broker Transmission Queue.


Service Broker: Receiving TCP/UDP messages from .Net TcpListener

With service broker and WCF and implementing message based SOA systems


Service Broker Messaging Issue with Real IP

Service Broker Messaging Issue with Real IP