Managing Service Broker Identities
Each database contains a unique identifier used for routing Service Broker messages to that database. This topic describes broker identities, how Service Broker protects against message misdirection, and the options available to manage broker identities.
Service Broker Identifiers
Each database contains a service broker identifier. 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 guarantee that all messages for a conversation are delivered to the same database. Therefore, the service broker identifier should be unique across all instances on the same network. Otherwise, messages may be misdirected.
Service Broker Message Delivery
To help guarantee that a service broker identifier is unique across all databases on a network, SQL Server provides a mechanism for deactivating Service Broker message delivery in a database. When message delivery is deactivated in a database, all messages sent from that database remain in the transmission queue for the database. Further, Service Broker does not consider services in that database to be available for receiving messages. These services are not considered when Service Broker routing locates a destination service within an instance.
Deactivating Service Broker message delivery allows you to safely attach a backup of a database for troubleshooting or data recovery purposes without running the risk of misdirected messages. The is_broker_enabled column of sys.databases shows the current state of Service Broker message delivery for each database.
SQL Server generates a new service broker identifier for each new database. Because the identifier is new, SQL Server can safely activate Service Broker message delivery in the new database. No other databases on the network should have the same service broker identifier.
When you attach or restore a database, use care to ensure that only one database with a given service broker identifier has message delivery active. Otherwise, messages may be misdirected and processing for a conversation may occur in the wrong copy of the database.
Managing Identifiers and Message Delivery
The CREATE DATABASE command, the ALTER DATABASE command, and the RESTORE DATABASE command contain options for activating Service Broker message delivery and for changing the service broker identifier for a database.
When you attach or restore a database, the service broker identifier and message delivery status are unchanged by default. In general, you do not change the service broker identifier when you restore a backup for recovery purposes, when you are configuring a mirrored pair, or when you are setting up log shipping for a standby server. When you are making a copy of the database, you change the instance identifier.
There are four options for managing identifiers and message delivery:
- ENABLE_BROKER. This option activates Service Broker message delivery, preserving the existing service broker identifier for the database.
Note
Enabling SQL Server Service Broker in any database require a database lock. To enable Service Broker in the msdb database, first stop SQL Server Agent, so Service Broker can obtain the necessary lock.
- DISABLE_BROKER. This option deactivates Service Broker message delivery, preserving the existing service broker identifier for the database.
- NEW_BROKER. This option activates Service Broker message delivery and creates a new service broker identifier for the database. This option ends any existing conversations in the database with an error, since these conversations do not use the new instance identifier.
- ERROR_BROKER_CONVERSATIONS. This option activates Service Broker message delivery, preserving the existing service broker identifier for the database. Service Broker ends all conversations in the database with an error.
However, regardless of the option specified, SQL Server does not allow two databases that have the same service broker identifier to both have message delivery active in the same SQL Server instance. If you attach a database with the same service broker identifier as an existing database, SQL Server deactivates Service Broker message delivery in the database being attached.
For more information on the options for attaching databases, see CREATE DATABASE (Transact-SQL) and ALTER DATABASE (Transact-SQL). For instructions on how to activate service broker message delivery in a database, see How to: Activate Service Broker Message Delivery in Databases (Transact-SQL).
See Also
Other Resources
ALTER DATABASE (Transact-SQL)
CREATE DATABASE (Transact-SQL)
RESTORE (Transact-SQL)
sys.databases (Transact-SQL)