Service Broker Demystified - SET ENABLE_BROKER vs NEW_BROKER

Service Broker can be really confusing and causes data professionals to shy away from using it.  One example is setting up SSB.  It's easy, until you need to make a copy of your database.  Then the fun begins.  In this post I'll explain when you want to use ENABLE_BROKER vs SET NEW_BROKER.

Do you think Service Broker is confusing?  If so, I started a blog series called Service Broker Demystified because SSB really isn't that difficult.  Today we are going to cover what happens when you restore a database that contains Service Broker objects.  When you run ALTER DATABASE SET NEW_BROKER, in other words enable SB for the first time...SQL Server creates a service_broker_guid for your database as well as a flag that indicates Service Broker is enabled.  You can also configure your SQL Server to have all new databases automatically have SSB enabled.  

In the screenshot you'll see that I have a number of databases that have SSB GUIDs, but SSB is not enabled on any of them.  

First, let's cover what the GUID is for.  There is no requirement that the service_broker_guid be unique on a given instance.  This is easy to prove.  I have a repro script that you can download and try for yourself.  Or you can just follow along with the screenshots.  

First I create a database called SBTestOriginal and enable SSB.  Then I verify that SSB is enabled and determine the GUID.  We take a backup and restore it on the same instance as a copy with a new name of SBTestNew.  

The process of restoring the database did NOT change the GUID...but it did disable SSB.  So, we'll need to fix it if we want to use our services and queues in that database.  

Let's assume your SSB infrastructure runs totally intra-database, which is the case for most people.  After restoring a db you probably just want to run ENABLE_BROKER.  But you can't...

The reason is that the "assigned GUID" for the copied db is the same as an existing GUID on the instance.  So, the correct process is to SET NEW_BROKER.  

Note that now SSB is "reset" and we have a brand new GUID.  

But this raises another question...if I am forced to run SET NEW_BROKER in a restored db, what happens to "in-flight" messages in existing queues?  Are they destroyed or do they restart themselves automatically once SSB is again enabled?  Before we run a test to see the behavior let's think about what should happen.  When you ENABLE_BROKER then everything should just pick up where it left off.  But NEW_BROKER implies that everything should be cleaned up.  This makes sense from a safety perspective too.  If you restore your prod db to a dev system you want your in-flight messages to be destroyed so you don't accidentally continue processing them on a dev instance.  Let's see if I'm right:  

We are going to take the existing example and merely create a test service and queue and then enqueue a message before we take the database backup and restore it. 

Note that our message was successfully enqueued and is waiting for processing.  Now we backup and restore the db as a COPY to the same instance and we SET NEW_BROKER.  And then we run some code to see if our message is still awaiting processing in the new db:  

The contents of the queue are destroyed when we SET NEW_BROKER, as expected.  Nothing in the queue and nothing "stuck" in sys.transmission_queue.  Note also that are queue is ready for processing.  The message is gone.  It isn't logged anywhere, it is just destroyed.  

In general I think this behavior is the desired behavior in most use cases.  But this behavior is important to understand.  Depending on your messaging infrastructure and design you may need to apply some "compensating" logic on your queues before you simply SET NEW_BROKER.  For instance, it is common to design workflows in SSB that entail many messages going back-and-forth between services.  In those cases you may have transactions in a goofy state when you re-enable SSB.  You need to think about this and work with your DBAs to determine what the correct process should be when you restore databases with SSB enabled.  

This doesn't necessarily mean manual intervention by a DBA.  I'm a strong advocate of automation and I've always used a "SSB enable" procedure that I tell my DBAs to run WHENEVER they restore a db.  A script like this should be responsible for

1. if needed, handling compensating logic BEFORE SET NEW_BROKER is run.  Once it is run any old messages are gone forever.  
2. smartly determining if ENABLE_BROKER can be run, or if NEW_BROKER is required.  
3. the db must be in SINGLE_USER mode (or equivalent) when these commands are run.  
4. you may also need to set TRUSTWORTHY flags and deal with encrypted database master keys, depending on your requirements.  

You can download the script I use to enable Service Broker on a restored db.  It doesn't handle every scenario above, but it gives you a framework to plug in values specific for your needs.  

Whether you are a DBA or a SSB designer you need to be aware of what happens when you restore your database.  It can be a bit tricky and you don't want to lose important message data.  

You have just read "Service Broker Demystified - SET ENABLE_BROKER vs NEW_BROKER" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.