Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Service Broker Alphabet Part 1

By Simon Sabin,

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
 

Part 2 is also available

Total article views: 4778 | Views in the last 30 days: 7
 
Related Articles
FORUM

Service Broker Messaging Issue with Real IP

Service Broker Messaging Issue with Real IP

FORUM

Sequence of message's transfer from one service broker to second service broker

Sequence of message's transfer from one service broker to second service broker

FORUM

Service Broker

Service Broker

FORUM

Errorlog: The query notification dialog on conversation handle ....

Errorlog: The query notification dialog on conversation handle ....

FORUM

Service broker help

Problems with service broker implementation

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones