Service Broker Setup Routine

I've been using Service Broker more and more and I love it.  In my last post on Service Broker I wrote about how I sold my client on using Service Broker.  After selling it I needed a simple method to enable it on all of our customer databases reliably.  There is a dearth of GUI and monitoring tools for Service Broker.  This is a problem for me because the support DBAs are mostly familiar with GUI tools.  Further, SB is kinda a new technology (for them) and they find it a little bit scary.  This means needless late night phone calls to my staff to troubleshoot relatively simple problems.  I've decided that, going-forward, whenever I implement something new in Service Broker I will also deliver a little monitoring tool that my support guys can use that will save my team some grief.  

In this post I'll cover my ServiceBrokerEnable script.  This script simply enables Service Broker on your db and ensures everything is up-and-running so you can begin installing your queues and services.  

Requirements

  • You can run into some serious problems when you ENABLE_BROKER on a database restored as a COPY on the same SQL instance.  This is because the broker GUID is no longer unique.  My routine looks for that and does a SET NEW_BROKER instead.  But to do this your database must be in SINGLE_USER mode so SB can get an exclusive db lock.  
  • Your database must also have its TRUSTWORTHY flag set, something that will not happen when your db is restored.  
  • I have instructed our support guys to simply run this new routine whenever they CREATE or RESTORE a database.  
  • The script is "properties-based", meaning that I can re-run it several times, even on a RUNNING system, and it will only do what it needs to do, if it needs to do it.  So, for example, it won't set my database to SINGLE_USER unless it has to and it won't rebuild my queues and services unless they do not exist in the correct, running state.  

You can download the code here.  Let me point out some of the key points:  

 

Note that we are checking if SB is enabled before doing any work.  We are also CATCHing the case where we cannot ENABLE_BROKER because of the duplicate GUID issue.  At the very end of the procedure I am turning on the TRUSTWORTHY flag and ensuring my db is owned by SA.  You may not need these last two steps, but our environment and setup requires it.  

In the next post I'll cover my [[Service Broker Monitoring Routine]].