Whenever a database with Service Broker enabled is restored, we notice that:

• The Service Broker is disabled in the restored copy
• The Service Broker GUID is NOT re-generated by default
• We need to manually re-enable Service Broker after the restore

The reason we need to manually re-enable Service Broker after the database restore is to prevent misdirection of broker messages to an incorrect copy of the database. I looked at this reason in my post here [Link].

After the post, I received a couple of questions on various channels. The most common question was:

We use Service Broker, and we need to have two identical copies of the same database for testing purposes. How can we ensure that Service Broker continues to work on both databases without any conflict?

The answer is actually quite simple. As mentioned in my previous post, we need to regenerate the Service Broker GUID. The scripts below show the various statements that one can use to enable Service Broker.

New Database AND/OR Restoring a copy of the database (for purposes other than recovery)

We need to generate a new Service Broker GUID in the following cases:

• Service Broker is being enabled in the database for the first time
• A copy of the database is being restored for purposes other than database recovery (testing, troubleshooting or other purposes)

--Enable the Broker and re-generate the Service Broker GUID
ALTER DATABASE AdventureWorks2012 SET NEW_BROKER;
GO

Please NOTE: Using NEW_BROKER will clear out any conversations in the database, however no error messages will be sent to the peer endpoint.

Restoring to a point-in-time

When restoring to a database to a point-in-time, it may happen that the existing conversations in the database may be invalid. In such cases, we need to reject/error these messages and send the appropriate message to the peer end-point.

Point-in-time restores are generally required during a database recovery which means that we cannot change the Service Broker GUID (else all messaging routes to the broker identifier will become invalid). To achieve this, we have the ERROR_BROKER_CONVERSATIONS option:

--Error out all conversations in the database
--Preserve the Broker GUID
--Enable the Broker message delivery
ALTER DATABASE AdventureWorks2012 SET ERROR_BROKER_CONVERSATIONS;
GO

Enabling Service Broker on an Existing Database

Enabling Service Broker on an existing database is quite simple – all one needs to do is use the ENABLE_BROKER option.

--Enable the Broker message delivery
--Preserve the Service Broker GUID
ALTER DATABASE AdventureWorks2012 SET ENABLE_BROKER;
GO

Disabling Service Broker on an Existing Database

The process of disabling the Service Broker on an existing database is similar to the enable process – one needs to do is use the DISABLE_BROKER option.

--Disable the Broker message delivery
--Preserve the Service Broker GUID
ALTER DATABASE AdventureWorks2012 SET DISABLE_BROKER;
GO

IMPORTANT NOTE:

Please note that when we enable the Service Broker, database lock is requested. Hence, please close all open connections to the database before attempting to enable the broker. If on the MSDB database, please also stop SQL Server Agent.

Further Reading

• Database Restore – manually enabling Service Broker – Msg 9772 [Link]
• Managing Service Broker Identities [Link]

Until we meet next time,

Be courteous. Drive responsibly.