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.

and RECEIVE

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

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