Service Broker As A Queuing Technology

Thanks to Eric V., Jennifer X., Hari, Sanjay and Mark M. for helping me get jump started with this technology!

It started out simple enough.  My first bug (‘issue’, ‘OFI’, ‘work item’) on the new team.  Ah, did they snooker me but good.   “Super Cool Service (server broker version) possible data loss” is how the title read.  Fixing the possible data loss scenario was the easy part.  Having to learn anything about Service Broker?  Ah, well that’s where our story begins…

I’ve only spent a week with this technology, so I’m sure I’m missing the finer points. In a nutshell,Service Broker is one option for transactional, message queuing for clients that have access to Sql Server (no ‘disconnected clients’ – though this might work using Sql Server Express on the client). SSSB provides the ordered delivery of messages and no practical limits to the size of the message; first in-first out (FIFO) order. Service Broker also supports remote transactional receives (so does MSMQ with Windows Server 2008 – but this is about Service Broker).

However, tooling support Service Broker is lagging.  Without proper tools (designers, wizards and debuggers) Service Broker applications can become complex quickly.  There is no real concept of tracking, journaling, or auditing built into to Service Broker.  That being said, if your comfortable with Sql Profiler you can probably get pretty far.  Then adding features like auditing, or journaling, of messages can be added in a typical Sql fashion. 

One thing that I think needs to be clarified is Service Broker being a pub/sub architecture.  Unless you consider pub/sub capability the ability to publish to a single service and receive by a single service.  Then yeah…you’ve got a pub/sub architecture.  If you consider pub/sub the ability to publish one event and [n] number of subscribers, then your going to need to look more into traditional MSMQ, or an actual broker like BizTalk.  The product may have ‘broker’ in the title, but I’m not quite sure what’s meant by that. 

The closest I’ve come in the last week to getting pub sub out of SB is to use a stored procedure on the receiving queue to fan out a message to additional subscribing services.  I’ve not been able to get this to work in anyway that feels natural.  That subscriber information has to be stored somewhere and again, no tooling.

One thing for sure, SB does have the ability to shutdown a queue after 5 failed attempts to receive a message.  That being the case, you’re going to need to check the status of your queues.

-- check status of queues
--
SELECT 
    [name], 
    is_activation_enabled, 
    is_receive_enabled, 
    is_enqueue_enabled, 
    is_retention_enabled 
FROM sys.service_queues WITH(NOLOCK)

Just like the referenced supporting links state, you’ll find yourself frequently referring to the sys.transmission_queue, which is like the ‘outgoing’ queue concept with MSMQ.  Watch the error_desc column.

When things go wrong, and they will, you’ll need a way to quickly clean up all the messages that are in a half-baked state.  This snippet can be used for any queue.  This is simply the best snippet to have in your pocket while learning Service Broker.

-- clean transmission queue; can be used for any queue
-
DECLARE @conversationHandle UNIQUEIDENTIFIER
WHILE EXISTS (SELECT 1 FROM sys.transmission_queue)
BEGIN
      SET @conversationHandle = (SELECT TOP 1 conversation_handle FROM sys.transmission_queue)
      END CONVERSATION @conversationHandle WITH CLEANUP
END

Want to know who your broker instance is conversing with at any given time?  This snippet will show both conversation end points. 

-- view all conversations and their states
--
SELECT * FROM sys.conversation_endpoints WITH(NOLOCK)

 

Want to know what the existing routes are for the current broker instance?

SELECT * FROM sys.routes WITH(NOLOCK)
    WHERE broker_instance IN (SELECT [service_broker_guid] FROM sys.databases WITH(NOLOCK))

 

Want to know what your service broker instance identifier is?

SELECT [name], service_broker_guid FROM sys.databases WITH(NOLOCK)

Service Broker and Xml Data

This caused no end of consternation initially so I thought I’d actually call it out here.  When creating a Service Broker message type, you have the option of specifying what validation, if any, to apply. You need to keep in mind what your option is, because it could have effects on both senders and receivers.

If VALIDATION=WELL_FORMED_XML and a sender sends an invalid xml message, using the NVARCHAR data type (which some DAL will do when best guessing data type), then this message will hang in the sys.transmission_queue and not be delivered to the receiving queue.  There will be no error message in the error_desc column either. 

-- send an invalid xml message
-- 

DECLARE @dialog_handle UNIQUEIDENTIFIER,
        @Message NVARCHAR(MAX) ;

-- here is our invalid message
--
SET @Message = '<message text="this is a test/>';


BEGIN DIALOG @dialog_handle
    FROM SERVICE [SenderService]
    TO SERVICE 'ReceiverService'
    ON CONTRACT [CustomerContract] ;    

SEND ON CONVERSATION @dialog_handle
    MESSAGE TYPE [Customer]
        (@Message) ;

END CONVERSATION @dialog_handle;

Changing the above snippet to use XML data type will prevent the scenario.  So, when dealing with xml data, use the xml data type and you will catch those invalid xml errors much earlier as assigning xml to an xml data type it must be well formed by default.  The other thing you can do is make sure your sending service (code, or sql) is at least making sure its valid xml.

Summary

Just to sum things up, Service Broker is an option when choosing a queuing technology on the Microsoft platform that will deliver on the following requirements:

• Handle message size greater than 4MB


• FIFO
  
• Remote, transactional receives
  
• Easy to make highly available due to very nature of any production Sql environment

Service Broker has also been suspected, but not confirmed, of the following adverse conditions:

• Hair loss


• Headaches
  
• Emotional despair

I started to title this post “The Little Queue That Almost Could”, then I thought that’s just being mean and showing off my ignorance of what I am sure is a wonderful technology.   As I get the opportunity to understand more, i’ll clear up any misconceptions here.

I am very interested in building BizTalk, err pub/sub and tracking capabilities around a Service Broker infrastructure, so I’ll likely be revisiting this topic again in the future.  If you have any suggestions, questions, corrections, or comments,  please reply to this thread!

References

I have several online references, as well as Sql Books Online, that I continually use during this learning adventure.  You can find these via http://delicious.com/zachbonham/ServiceBroker.