In most development and QA environments, one would find that a baseline copy of the database is restored and upgraded again to “begin from a clean state”. If your database architecture uses service broker to carry out background processes, you would have observed that whenever the database is restored on the same instance or on a different instance, we need to re-enable the broker manually:

ALTER DATABASE AdventureWorks2012 SET ENABLE_BROKER;
GO

The Question

Our database architecture uses Service Broker. Recently, we encountered this situation in a test lab where a production database was brought in-house for some testing, and that got us thinking:

Why do we need to manually re-enable Service Broker on a restored database?

Answer

The answer is available (as always) in the MSDN documentation, the extracts from which are shown below.

• Service Broker routing relies on a unique identifier in each database to correctly deliver messages
• Each database contains a Service Broker identifier that distinguishes it from all other databases in the network. The service_broker_guid column of the sys.databases catalog view shows the Service Broker identifier for each database in the instance
• Service Broker routing uses the Service Broker identifier to ensure that all messages for a conversation are delivered to the same database
• To correctly support message delivery, each Service Broker identifier should be unique across all instances of the Database Engine on the same network. Otherwise, messages could be misdirected. When a new database is created, it is assigned a new Service Broker identifier that should be unique in the network. The identifier is restored when the database is either restored or attached

If the restore is intended to replace the original database (also applicable if the database is being moved to a different instance), then we would not want the Service Broker identifier to change. However, if the purpose of restoring the backup is to create a copy of the database, then the identifier should be changed.

To provide a clear explanation, I have come up with a small demo below:

Base State

To begin with, let us create a test database and enable service broker on the same.

USE master;
GO

--Create a test database
CREATE DATABASE ServiceBrokerFlagTesting;
GO

--Enable Service Broker on the database
ALTER DATABASE ServiceBrokerFlagTesting SET ENABLE_BROKER;
GO

--Check the database properties
SELECT 'Before Backup' AS Stage,
       sd.is_broker_enabled AS IsServiceBrokerEnabled,
       sd.service_broker_guid AS ServiceBrokerGUID,
       sd.name AS DatabaseName, 
       sd.database_id AS DatabaseId
FROM sys.databases AS sd WHERE sd.name = 'ServiceBrokerFlagTesting';
GO

Let us now create a backup of this database and drop the database so that we can restore it again from the backup. (Simple scripts to back-up and restore databases from a UNC path and/or disk are available here. You may customize them for your requirements.)

--Backup the database
USE master;
GO
EXEC SP_ADDUMPDEVICE 'disk',
                     'networkdrive',
                     'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLBackupServiceBrokerFlagTesting.bak'

BACKUP DATABASE ServiceBrokerFlagTesting TO networkdrive;

EXEC SP_DROPDEVICE 'networkdrive';
GO

--Drop the database
ALTER DATABASE ServiceBrokerFlagTesting SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE ServiceBrokerFlagTesting;
GO

Restore 2 copies from the same backup and query Service Broker Information

Now, let us restore two copies of database from the same backup and query the Service Broker information.

USE master;
GO
--Now, restore the backup to two databases
EXEC SP_ADDUMPDEVICE 'disk',
                     'networkdrive',
                     'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLBackupServiceBrokerFlagTesting.bak';

RESTORE DATABASE ServiceBrokerFlagTesting FROM networkdrive
WITH
MOVE 'ServiceBrokerFlagTesting' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerFlagTesting.mdf',
MOVE 'ServiceBrokerFlagTesting_log' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerFlagTesting_Log.ldf';

RESTORE DATABASE ServiceBrokerDummy FROM networkdrive
WITH
MOVE 'ServiceBrokerFlagTesting' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerDummy.mdf',
MOVE 'ServiceBrokerFlagTesting_log' 
  TO 'C:SQL DatabasesSQL2012MSSQL11.SQL2K12MSSQLDATAServiceBrokerDummy_log.ldf';

EXEC SP_DROPDEVICE 'networkdrive';
GO

--Check the Service Broker status - notice the Broker GUID
SELECT 'After Restore' AS Stage,
       sd.is_broker_enabled AS IsServiceBrokerEnabled,
       sd.service_broker_guid AS ServiceBrokerGUID,
       sd.name AS DatabaseName, 
       sd.database_id AS DatabaseId
FROM sys.databases AS sd 
WHERE sd.name = 'ServiceBrokerFlagTesting' 
   OR sd.name = 'ServiceBrokerDummy';
GO

As can be seen from the screenshot above, both the databases have the same value for the service_broker_guid. Also, notice that Service Broker is not enabled in either of the databases.

Enabling Service Broker and Error Msg 9772

Now, assume that both databases can be enabled to process the broker messages. If this is allowed, then we would see misdirected messages, i.e. a message that may be bound for database id 9 is being processed by database id 8 which would not be good. It is just like having two roads on opposite ends of the city leading to confusion amongst the motorists.

It is for this reason that SQL Server by default always disables message delivery when a database is restored or is taking part in mirroring and/or is the destination in a log shipping scenario.

Attempting to enable the service broker for both databases results in the following error message when the 2nd database is being processed:

--Enable the Broker
ALTER DATABASE ServiceBrokerFlagTesting SET ENABLE_BROKER;
GO

/*
Msg 9772, Level 16, State 1, Line 1
The Service Broker in database "ServiceBrokerDummy" cannot be enabled because there is already an enabled Service Broker with the same ID.
*/
ALTER DATABASE ServiceBrokerDummy SET ENABLE_BROKER;
GO

Msg 9772, Level 16, State 1, Line 1

The Service Broker in database "ServiceBrokerDummy" cannot be enabled because there is already an enabled Service Broker with the same ID.

Summary

When working with Service Broker, one needs to be careful about message delivery. If a service broker enabled database needs to be restored for troubleshooting and/or data recovery purposes, then:

• When a database is restored/attached, the message delivery to the database would always be disabled
• If Service Broker needs to be enabled for a copy of the same database on the same network, then the service broker GUID must be changed via the use of the NEW_BROKER option. This option activates the broker and also generates a new service broker identifier for the database
• Care must be taken to ensure that only one database with a given Service Broker identifier has message delivery active. Otherwise messages would be misdirected to a wrong copy of the database

Further Reading

• Managing Service Broker Identities [Link]
• Service Broker Routing [Link]

Until we meet next time,

Be courteous. Drive responsibly.