Setup and Configure Service Broker Objects Reliably and Repeatably

I mention repeatedly in my MD3 blog posts that DDL is esoteric and error-prone.  It would be so much easier if we could just "declare" what we want our objects to look like and then SQL Server just "did the needful" and gave us what we wanted.  I attempted to do just that with my MD3 (Metadata Driven Database Deployment) utility for properties-based database deployments.  I've tried to follow that paradigm and create a tool to handle SQL Service Broker (SSB) object deployment similarly.  The problem is that SSB is a highly custom solution with lots of parts that have interdependencies that make a declarative approach to SSB object creation difficult.  However, there are patterns that, if used properly, will greatly improved the repeatability of your SSB deployments.  

The SSB Configure Shell

I guarantee you that you won't be able to just use this procedure out-of-the-box in your environment.  Instead, this procedure is meant to be a pattern to use that will help you to reliably and consistently deploy your SSB objects.  Most SSB implementations can use this pattern with very little modification.  

First, get the code.

Next, note that SHELL is meant to be a token replacement.  If you were creating a service broker infrastructure for a Finance application you may start by replacing SHELL with Finance in the script.  

The Basic Pattern

The basic pattern for SSB objects is to create a single script that handles creating, modifying, dropping, and general "hygiene" of all of your SSB objects for a given SSB "application".  For instance, you don't want to have to hunt for your scripts to drop a QUEUE only to realize that you have to drop the SERVICE attached to that queue first.  Likewise, when setting up your SSB objects you need to create them in dependent order (QUEUEs before SERVICEs).  Here are the available options when you call the configure procedure:  

The first option is to perform a SETUP...ie, we want to create the SSB objects, if they do not exist, in the database.  If they do exist we want to modify them if any of their "properties" have changed (ie, new service attached to queue, new activator for a queue).  I discuss SETUP more in the next section.  

Conversely, we have TEARDOWN FORCE OVERRIDE which will totally remove this SSB application without any prompting.  If there were any unprocessed messages in the queue they are GONE.  This is great for dev environments that need to be reset when you find bugs in your code or are refactoring a feature.  This will remove any messages from sys.conversation_endpoints and sys.transmission_queue that are not CLOSED

TEARDOWN will only remove the SSB application if there are no unprocessed data in any of the underlying queues.  This is the "safe" option that I use when I need to upgrade a SSB application.  

DISABLE ALL QUEUES disables activation.  This allows the queues to be "paused" while a new version of an activator is deployed without dealing with possible application errors due to queues being offline or old activators processing new business logic.  

Finally, STALLED MESSAGE CLEANOUT is valuable during development when you realize that something you did is causing messages to get "stuck" in sys.conversation_endpoints, probably because of something you have misconfigured.  

By placing all of the code in one place you, or more appropriately your administrator, can run these commands quickly and get an environment "reset" properly.  

Deeper Dive into Setup

When we set up a new SSB "application" we have to remember to create things in dependent order.  The SSB_SHELL_Configure pattern handles this for you.  

There is a section for first creating MESSAGE TYPEs, then CONTRACTs using those MTs.  These are "optional" SSB objects.  

We then create the QUEUEs and SERVICEs, any activators, and then enable activation (if needed).  

Summary

SSB is an esoteric subject.  Deploying SSB objects can be difficult to get correct due to order dependency within SSB objects.  Dropping and upgrading SSB objects likewise consider special consideration due to object dependencies.  Likewise, "upgrading" SSB objects requires that queues properly "drain" before applying the latest code (usually, it depends on your situation).  My SSB deployment pattern tries to handle these situations for you.  It is meant for the lowest-common-denominator SSB deployments.  If you use remote SSB instances and endpoints then you'll need to do a bit more work.  But the base "pattern" will still work for you.  

This pattern is especially useful in development environments where it is easy to make modifications that totally corrupt your SSB objects and you need to start over with purged queues.  

You have just read "[[Setup and Configure Service Broker Objects Reliably and Repeatably]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.