Service Broker Demystified - Can I model monologs? Yes you can!

The most common use case for Service Broker is to run a stored procedure asynchronously.  This is best modeled as a monolog, commonly known in the SQL Server world as "fire-and-forget".  Every other messaging system (MSMQ, JMS, Rabbit MQ) support monologs natively, in fact they are the default.  But not Service Broker.  If you try to model a monolog you'll find that it is rife with danger.  In this post I'll show you how you can, safely, model a monolog.  

People claim they avoid Service Broker because it is too confusing. I started a blog series called Service Broker Demystified because SB really isn't that difficult. Today we are going to cover how to model a monolog in SSB.  Safely.  Let's take a step back and look at how SSB message communications work.

Service Broker conversations are always dialogs (that's why the syntax is BEGIN DIALOG CONVERSATION).  In SQL Server a dialog involves EXACTLY two parties...one called the Initiator and the other called the Target.  At a bare minimum the conversation goes like this:

1. Initiator begins dialog and sends a message
2. Target receives the message, processes it, and acknowledges it.  The target always ends the conversation first.  
3. Initiator ends the dialog on the initiator side.  

In my post on CLOSED conversations I discussed why it is so important that the target always ends the conversation first.  If you don't remember this then you probably have modeled the fire-and-forget pattern which is dangerous.  I covered fire-and-forget in my last post.  

But my experience is that many (most) SSB implementations really should be simple f&f patterns.  In fact, this is the most common messaging pattern for other messaging technologies like MSMQ, RabbitMQ, and JMS, to name but a few.  The most common use case I've seen for SSB is where you need to send a message to another SSB service to initiate some asynchronous processing after a certain data event occurs.  For instance, a trigger sees a data change which pushes a message onto a queue to launch an ETL process.  The initiator (the trigger) couldn't care less when the ETL process completes, or even if completes without error...that is some other process' job to monitor that.  

What I just described is a monolog.  A one-way conversation.  A one-sided dialog.  That's f&f.  That's not modelable out-of-the-box with SSB.  But it's quite easy to do.  Again, it's the default for every other messaging technology that I know of.  

You can model monologs in Service Broker just like in other messaging systems, but you can't do it with "fire and forget"...or at least you shouldn't.  For a monolog in SSB you really need to have an activator on the initiator side that is ENDing CONVERSATION.  But remember, this must be done after the target does its processing and does an END CONVERSATION first.  The target always ends the conversation first.  That is the key to a safe monolog.

Let's look at the use case where "I need to start a stored procedure asynchronously from a trigger."  Invariably a google search will yield that some folks model this by launching SQL Agent jobs.  This is true f&f.  It works great.  But it's not very elegant IMHO.  Why?  

• I have to rely on SQLAgent and the msdb database.  
• A simple restore of a database doesn't get me a working system without the need to recreate the job.  If the job name changes then I need to keep that in sync with the trigger. 
• I need to "process key" the SQL Agent job (ie, give it a name that is "unique" across what could be many instances of my db installed on that server).  SQL Agent has affinity to the instance, whereas SSB has affinity to the individual database.  

So, further down in the google search results someone will suggest Service Broker.  This is much more elegant.  But it is a bit confusing for SSB noobs.  Here's why:  

• The novice realizes subconsciously that this is "fire-and-forget" so they immediately ask, "why do I need the FROM SERVICE clause in the dialog statement?  I don't really care where the message came from."
• And then, "Why do I need a queue to send a message?  I just want to send it and move along to the next task."
• The fact is with SSB this can't be modeled exactly like a SQLAgent job

Here is how I always model a monolog.  If you follow this pattern it will always work.  Safely.  Download the code if you want to follow along.  First, we need two queues and two services.  To properly model a monolog you still need two of each of these objects.  The "sender" really does nothing important in the monolog, it's simply there because there is no other way to do monologs properly.  

The SenderQ, while not functionally important, does serve a purpose...it must "acknowledge" the receipt of the message from the ReceiverQ.  So let's create a very rudimentary activator proc whose sole purpose is to acknowledge the receipt of the processed message from the ReceiverQ.  All this proc really needs to do is END CONVERSATION on the sending side.  

Once the activator proc is created we "attach" it to the SenderQ.  For purposes of the demo we'll temporarily disable activation.  

With everything set up let's see this in action.  Let's say you have a long-running process that you want to launch asynchronously and in an autonomous transaction.  The code below will do this.  We simply need to enqueue a message from the SenderSvc to the ReceiverSvc.  This can be from our Java app, a stored procedure, or even a trigger.  

The "message payload" is whatever work we want to do.  Note that we NEVER END CONVERSATION from the sender first.  The target needs to be the first to END CONVERSATION, otherwise you risk fire-and-forget problems.  

Let's check what happened:  

The message was successfully enqueued to the ReceiverQ and is waiting to be processed.  Normally if we had the activator proc enabled it would immediately pick up the message and process it.  But we have that disabled for this demo.  Let's look at how the ReceiverQ would process the message:

Line 95 is where we would do whatever asynchronous work is required.  Note that we are following our rule that we always END CONVERSATION on the receiver-side first.  

After "processing" we query the queues and note that the message was removed from the ReceiverQ and a new message of type EndDialog was placed back into the SenderQ.  

This the weird anamoly with Service Broker "monologs"...the SenderQ must process that acknowledgement.  This is where a SSB monolog is really a dialogue.  No other messaging frameworks work this way.  This is where the activator comes into play.  We left the SenderQ's activator disabled so we could see the EndDialog sit in the queue.  Let's enable the activator and see what happens:

Here we have enabled the activator and we see that the EndDialog was automatically processed.  

Summary

The most common messaging pattern is the fire-and-forget pattern, also called a monolog.  Unfortunately SSB doesn't natively support monologs.  The correct pattern is akin to a "one-way dialog".  We simply attach an activator proc to the sending queue.  It's sole responsibility is to acknowledge that the target service has processed the message.  Armed with this information you can now safely model monolog patterns in Service Broker.  

You have just read "Service Broker Demystified - Can I model monologs?  Yes you can!" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.