Saturday, August 16, 2008

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
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
WHILE EXISTS (SELECT 1 FROM sys.transmission_queue)
SET @conversationHandle = (SELECT TOP 1 conversation_handle FROM sys.transmission_queue)

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?

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

@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] ;

(@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.


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!


I have several online references, as well as Sql Books Online, that I continually use during this learning adventure.  You can find these via

Monday, August 11, 2008

DFWCSUG 08/13/2008 Meeting - Workflow as a Service

Join us at the DFW Connected Systems User Group meeting this Wednesday as Anderson Imes presents on the topic of Workflow as a Service.

Don't forget, FREE food, drinks and a prize drawing!

If you haven't already, please take a moment to Join DFWCSUG on CodeZone!

Am I a Zune or an iPod?

Just give me my music and nobody gets hurt.

I own a Zune, it was given to me.  My wife owns a late generation iPod Nano Video.  The Zune software is hands down better than the iPod iTunes, which I consider just a hair breadth away from being crapware, and the device UI is every bit as good as the iPod (IMHO).  You can develop for it, which looks like is just starting to take off.  My 30GB Zune is, however, one ugly brick - but its first generation. 

Would I buy a new Zune?  Probably, but if I did, I would purchase a smaller edition, maybe the 8GB - I am not a video watcher on that postage stamp, so its just extra weight I'm going to carry around.  I can ditch that by slimming down and still hold all the essential music I might need.  That brings me on par with the 8GB iPod Nano Video.  Though having two media devices like this is not without compatibility problems around the 'media library'.

SuperSite blog post AntiZune spurred this post and you can check out the Zune vs. iPod comparison.