SQLServerCentral Article

The Service Broker Alphabet Part 1


Service Broker Alphabet A - O

Service Broker is an amazing new feature in SQL Server. There are many opportunties

where Service Broker to be used. One challenge is that Service Broker introduces

a raft of new terminology. Here is the first half of the Service Broker alphabet.

A is for Asynchronous

Once a message has been sent by the client the processing of the message is asynchronous.

This means the client can get on and do what it needs to rather than wait.

and Activation

This is the mechanism by which SQL Server Service Broker starts an application or

stored procedure in response to messages arriving on a queue.

B is for Batch

The receive mechanism will receive as many messages as you want from a queue for

a single conversation. This means that if you send messages using the same conversation

handle you can then use a single receive statement to retrieve a batch

of messages,

C is for Conversation

A conversation is how messages are grouped in the Service Broker world. Messages

to between services are done in the context of a conversation. You can many conversations

between the same services at the same time.

and Contract

A contract is the definition of what the messages will look like that are sent

between services

and Case Sensitive

Object names in the Service Broker world are case sensitive. The reason is that the

resolution of names is done between different databases and different server instances.

These can have different collations and so to avoid complications all lookups for

Service Broker objects is done using binary comparison.

This applies to Services and Contracts

D is for DEFAULT contact

If you are not worried about verifying the content of your messgaes against an XSD

schema then you use the DEFAULT contract. This is also used if your messages are

not XML data.

E is for


Messages are, by default, transmitted between services encrypted. This requires

you setup an master key for your database. If you don't need to use encryption make

sure to create your dialogs with encryption off. If you don't, your messages will

end up in the sys.transmission_queue. You can read more here Service Broker Dialog Security  

F is for Forwarding

Service Broker can be configured as a forwarding mechanism. This allows you to move

messages to different networks that are separated

and Fragmentation

There are many optimisations that are built into the Service Broker solution. One

of these is fragmentation. Unlike that fragmentation on your disk drives that has

your Manager jumping for his favourite Disk Defragger, this fragmentation is good

for your system. Service Broker breaks your message into fragments to send them

to send them accross the network. This is essential if you are sending large files

and even more true if you have a flaky network, have you ever tried sending a large

file between 2 servers across a flaky network. The slightest glitch causes the copy

to fail and so you have to restart, by breaking messages into fragments Service

Broker avoids this issue.

G is for Group

Conversations can be grouped together using conversation groups.

H is for Handle

The conversation handle is the identifier for a conversation. Multiple messages

can be sent using the same handle in which case the messages are grouped together

and can be received in one batch at the target service.

I is for Internal Tables

A queue is simply an internal table where messages are stored. The names of the

internal tables can be obtained from the system catalogs. http://msdn2.microsoft.com/en-us/library/ms366343.aspx

and Initiator

There are two parties involved in a Service Broker conversation, the initiator and

the target. The initiator sets up a conversation with the target and sends a message.

J is JOINs

The receive statement allows for you to JOIN to other tables to combine information

in the messages with data from your normal relational tables

K is Key

To use encryption you have to have a master key defined for the database. This is

so that the session key for the messages can be encrypted.

L is for Lifetime

A messages sent using a dialog, a dialog can be used during its lifetime. Once the

lifetime is reached both sides of the dialog refuse to send new messages with the


M is for Message

The message is the data that is sent as part of a conversation between 2 services.

The message can be anything and is stored in a varbinary(max)

and MSDB

MSDB is used to store messages that are in transmission in and out of the a SQL

Server instance and for forwarding messages.


When testing Service Broker applications one often ends up in a bit of a mess, especially

if you get the case of contracts and services wrong or you forget to turn encryption

off, or you get your activation wrong. The simple way to rectify this is to issue

an ALTER DATABASE along with NEW_BROKER. This clears all queues and generates a

new Service Broker GUID.

O is for Optimizations

The standard unit in Service Broker is a conversation group. This is the level of

locking. For this reason the creating of a new conversation (dialog) has an amount

of overhead associated to create all the constructs for it. To optimise the delivery

of multiple messages you can reuse a dialog, this means you are not burdened with

the overhead of creating a conversation every time you send a message.

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


Part 2 is also available


1 (1)

You rated this post out of 5. Change rating




1 (1)

You rated this post out of 5. Change rating