THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Service Broker Basics

I'm currently implementing a Service Broker solution at a client site, and it's been an interesting challenge, because there's not a lot of information out there to help guide you through the process. Here I'd like to walk you through the basics.

Message Types. Service Broker sends messages asynchronously from one database to another. You can set it up to send messages between databases on a single server, or between SQL Server instances on a Windows server, or between different physical servers, whether or not they're in the same domain. Essentially Service Broker works at the database level, the rest is handled through routing, which I'll address in another post.

The important thing to remember is that Service Broker sends and receives messages, and then your applications (or stored procedures) handle those messages in some way. It handles them asynchronously, so the sending side doesn't have to wait for the receiving side to acknowledge the message, and it handles them sequentially, so the messages will always arrive in the order in which they've been sent.

Many of the examples you'll see use message types like "REQUESTMESSAGE" and "REPLYMESSAGE". To me this is a disservice, because it doesn't help you see the different ways you can use Service Broker to solve your business problems. At my client site the message types indicate the content of the message, so the receiving side can use the type to determine the action to take when the message is received. Service Broker has a built-in acknowledgement process, so you don't need to specifically acknowledge a message, unless the application needs it. As long as the communication channels are open, the message will be delivered.

CREATE MESSAGE TYPE [//AWSync/Sync/HumanResourcesEmployee]
AUTHORIZATION dbo
VALIDATION = WELL_FORMED_XML
GO

Contracts. Once you've defined the types of messages that can be sent, you need to define how they'll be delivered. Contracts define what message types are allowed to be sent, and in which direction. This means that Service Broker is secure in that it won't process any messages types not defined in a contract, so rogue processes that attempt to try a type of SQL Injection attack against Service Broker will fail.

CREATE CONTRACT [//AWSync/Sync/IntContract]
AUTHORIZATION dbo
( [//AWSync/Sync/HumanResourcesEmployee] SENT BY ANY,
[//AWSync/Sync/PersonContact] SENT BY ANY,
[//AWSync/Sync/PurchasingVendor] SENT BY ANY )
GO

Queues. Once the contract is defined, you can define the queue on which the messages are sent and received. The queue also defines (if you want) an automated process that will handle the messages it receives. In your Transact-SQL code you retrieve messages from the queue in the same way you read data from a table - in fact, the queue behaves just like a table in your database.

CREATE QUEUE IntQueue
WITH
STATUS = ON,
RETENTION = OFF
GO

Services. The service is the glue which assigns the contract to the queue. It performs the work of actually sending the messages on the queue to their destination and receiving the messages coming from other senders.

CREATE SERVICE [//IntSite/Sync/IntService]
AUTHORIZATION IntUser
ON QUEUE IntQueue
([//AWSync/Sync/IntContract])
GO

Conversations. In its simplest form, the last thing we need is to send the message. We do that via a conversation, which is referred to in Service Broker as a DIALOG CONVERSATION or simply a DIALOG. You specify the source and destination service name, and a conversation handle (a GUID) is returned, then you SEND ON CONVERSATION using that conversation handle. The message body is usually in an XML form, and for security purposes should be encrypted.

BEGIN DIALOG @InitDlgHandle
FROM SERVICE [//IntSite/Sync/IntService]
TO SERVICE N'//ExtSite/Sync/IntService'
ON CONTRACT [//AWSync/Sync/IntContract]
WITH
ENCRYPTION = ON;

SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE [//AWSync/Sync/HumanResourcesEmployee]
(@ChangeMsg);

Finally, you need to be able to receive the messages. Like I mentioned earlier, reading from a queue is like reading from a table, but there are some additional features in Transact-SQL to facilitate message handling. Specifically, there's a special form of the WAITFOR command which will wait for either the arrival of a message, or timeout after a specified number of milliseconds.

WAITFOR (
RECEIVE TOP(1)
@ch = conversation_handle,
@service_name = service_name,
@service_contract_name = service_contract_name,
@messagetypename = message_type_name,
@messagebody = CAST(message_body AS XML)
FROM ExtQueue
), TIMEOUT 60000

With these components you can set up messaging within a single instance of SQL Server. In my next post I'll discuss the additional plumbing required to communicate between separate instances.

Allen

Published Wednesday, January 06, 2010 12:59 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Allen White said:

Last week I introduced the Service Broker Basics . Today I'd like to cover some of the "plumbing" - the

January 12, 2010 10:44 AM
 

Jon said:

Great information, But just a quick question: Do I run these  scripts on both servers (including remote?) And would 'IntUser'  be my server log-in name?

Lastly if you have time, how do I alter these files after I created them? They do not appear in SQL server express 2005.

Thank you for taking the time to respond to my questions.

-Jon

April 6, 2011 3:45 PM
 

Himanshu said:

Very nice article indeed..! It's much helpful.

I have a one query :

In my SB design, I need to make an asynchronous calls and needed some work to be done in background (Inside SQL Server only, like updating tables). My requirement doesn't suits for creating 2 different SB services as only one service would suffice. Note that there is no acknowledgement required (Only one-way data push). I designed the scenario with having only one SB Service, and while creating a conversation dialog, I assigned same service name to the 'From' & 'To' clauses. Program pushes data to the SB queue and activator will activate associated Store Proc.. All with a single service. It works just fine.

Your inputs would be appreciated if you see any issues/suggestions on performance or anything else with this design.

- Himanshu

January 4, 2012 4:09 AM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement