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.
You have just read "[[Service Broker Demystified - How to Radically Simplify SB]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.
Dave Wentzel CONTENT
sql server service broker service broker demystified