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
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
SET NEW_BROKERis run. Once it is run any old messages are gone forever.
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.
Dave Wentzel CONTENT
sql server service broker service broker demystified