Setting up Service Broker objects can be challenging. Objects need to be deployed in the correct dependency order. And then if something goes wrong you need to determine what objects need to be recreated as well as what messages may still be "stalled" and waiting for processing. In this post I'll show you my "pattern" script that helps to ease these burdens. Using the concept of "script idempotency" I can guarantee that all objects are deployed and running correctly. At any time.
Service Broker (SSB) can be confusing. I started a blog series called Service Broker Demystified because SSB really isn't that tough if you don't get lost in the weeds. One of the biggest reasons Service Broker is so scary is because there is no (good) GUI to set it up. In this post I'll cover how to set up your SSB infrastructure reliably without a GUI. Here are some patterns I like to follow:
- Make sure your scripts are idempotent (rerunnable without side-effects...ie, they shouldn't fail if a component already exists).
- Scripts should always succeed. Manual intervention should never be tolerated. Nor should errors.
- Follow the correct object dependency order when you create your objects. This is slightly more complicated than you may at first think. I'll discuss that below.
- Your script should be able to both SETUP and TEARDOWN your SSB objects. The TEARDOWN option is useful mostly for dev and test instances where your code is more volatile and fragile. TEARDOWN should also have sub-options that "gracefully" tears down your objects, but only if the queues are drained, and then the NUCLEAR option which simply resets the environment and purges semi-processed messages. In this case we expect that messages (data) may be lost. You want all of this coded BEFORE you actually need it.
If you follow this pattern you'll have a testable, repeatable deployment.
Here is the "shell" of the script I use to reliably and repeatably set up my Service Broker objects. This may not cover all of your needs, but it should cover most of the objects you'll need. It is designed so that you can replace "SHELL" with the names of your objects. Let's look at the major sections.
The API for setting up and manipulating your SSB objects should cover these items:
Setup does more than just set up your objects. It needs to be coded as idempotent (in other words
CREATE OR REPLACE <object>). When coded this way your SETUP process can be run at any time, even if someone simply removed an activator proc. A good SETUP process should have some basic testing at the end to ensure that things are actually working. "Tracer messages" that you can send through the system are a good practice to have, if your design can tolerate them.
I'll cover the SETUP section below in far more detail.
DISABLE ALL QUEUES
This option will stop activator procs from firing. This should be run whenever you need to ALTER a component of your SSB design or whenever you need to tear it down. If your activator starts throwing errors you want your queues to be able to still receive messages, you just don't want them processed.
TEARDOWN (WITH FORCE OPTION)
Code that tears down your setup is valuable in testing environments where code is volatile. Not every developer is an SSB expert and may not know how to properly "reset" your SSB infrastructure if a code change accidentally caused poison messages. A
FORCE option allows you to quickly reset everything while allowing data loss. Rarely should an option like this be used in a production environment unless you know what you are doing and can recover.
TEARDOWN without the
FORCE option is useful whenever you need to radically alter your SSB design and objects. In this case you would quiesce your system, run
TEARDOWN, then run
SETUP with the new SSB objects.
STALLED MESSAGE CLEANOUT
Sometimes you can't recreate your SSB objects because you have unprocessed messages that you don't care about. This option simply sets the state of those messages to
CLOSED and does
END CONVERSATION WITH CLEANUP. You could make this part of the TEARDOWN FORCE option and save a step. I keep this separate because I may want to recreate my SSB objects and then have a routine that attempts to reprocess those "limbo" messages. It's really up to you and your use case requirements.
...Back to SETUP
Obviously when setting up your SSB objects you have to do things in object-dependent order. You'll note that the "token" I use in my pattern script is SHELL. Simply performing a FIND/REPLACE on SHELL will show you everything that needs to change in the pattern. Here's an example that creates SSB objects in dependency order, in an idempotent manner...
If you use activated queues you'll want to install a "shell" activator proc if your production activator proc is stored elsewhere in your VCS. We want to have at least a shell activator installed with this pattern that way we can minimally enable activation without context switching between this script and some other script to get the actual code for the activator proc.
The TRACER TOKEN Option
I don't have this option in my pattern because its design is highly dependent on your use case. A TRACER TOKEN is a simple message that you can send through your entire SSB infrastructure to ensure it is being processed. For example, let's say your SSB design is that a message arrives in a queue, is read by an activator, is sent to another queue for bulk processing on another server. A TRACER TOKEN would be sent to the first queue in the workflow. From there a monitor would wake up every few seconds and poll to see if the tracer message was received at various stages. Your creativity is key to setting up a good tracer token system. The best systems will be able to tell you not just where things have failed but any latency as well. MS SQL Server replication has the concept of tracer tokens, start your research there for some good ideas.
This is just the tip of the iceberg for a good pattern for SSB object deployment. This only covers the basic SSB objects. Your design may require more objects like
MESSAGE TYPEs, and
CONTRACTS. This is simply a pattern that is helpful to ease deployment burdens. A side-effect of a good deployment pattern is it will aid you when you need to "reset" a dev environment that stopped processing messages. Finally a good deployment pattern is to test your design using a TRACER message after the deployment is complete.
sql server service broker service broker demystified