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
Encryption
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
dialog.
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.
N is for NEW_BROKER
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
http://sqlblogcasts.com/blogs/simons
http://www.sqlservicebroker.com/
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=91
http://blogs.msdn.com/remusrusanu
Books
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
