Service Broker Alphabet P-Z
Following on from my first half of the Service Broker alphabet, here is the concluding
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.
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
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.
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.
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
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.
The service that receives messages from the initiator is referred to as the Target
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
Z is for Zone
Due to this being a communication process across servers, across timezones the time
Zone for all dates is UTC
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