Author: Michael Thomassy

Technical Reviewers: Sanjay Mishra, Burzin Patel, Stuart Ozer

A Deployment Method: Copying Databases

When dealing with many large customers, they often develop new and interesting ways of using technology and deploying it. One such case concerning service broker is with large, scale-out deployments consisting of hundreds of servers where the same database will be copied and deployed many times over. This database will contain all of this particular customer's service broker standards and custom built pieces for how broker will operate in their environment. As a package this database can be easily copied and deployed across many instances and servers through 1) "copy & attach," or 2) backup & restore. The caveat for this approach is that each of these databases will contain the same service broker identity. Therefore, we need to reset it. For more details on Managing Service Broker Identities see Books Online (BOL):

http://msdn.microsoft.com/en-us/library/ms166057(SQL.100).aspx

We will get to the relevance of having an actual globally unique service broker identity shortly. However, it's important to recognize the database properties that are modified when a database is attached or restored. For our discussion relevant to service broker, I'll point out that the following values are set to 0 when the database is attached or restored:

• is_broker_enabled
• is_honor_broker_priority_on
• is_trustworthy_on

You can view database properties by querying sys.databases, see BOL:

http://msdn.microsoft.com/en-us/library/ms178534(SQL.100).aspx

SELECT
     name
     ,is_broker_enabled
     ,is_honor_broker_priority_on
     ,is_trustworthy_on
     ,service_broker_guid
FROM sys.databases

Just after detaching, making a couple copies and attaching the 3 databases, you’ll notice that all of my databases have the same service_broker_guid. There is no way to know the purpose, function or environment in which the database is attached or restored. This is why service broker is disabled by default. Now that the database has been duplicated, we need to reset the service broker identity as follows:

ALTER DATABASE [MySSBDBCopy1] SET NEW_BROKER
ALTER DATABASE [MySSBDBCopy2] SET NEW_BROKER
GO
SELECT
     name
     ,is_broker_enabled
     ,is_honor_broker_priority_on
     ,is_trustworthy_on
     ,service_broker_guid
FROM sys.databases

Giving the database a new service broker identity will enable service broker (is_broker_enabled=1) and will remove all service broker messages and conversations in the database with sending end dialog messages. Again, in our scenario since we are deploying a "standards" database we will assume there are no messages or conversations in the database.

Service Broker Diagnostic Utility

Having 1 or more databases with the same service broker guid may not seem problematic. However, service broker does assume that this guid is globally unique across "time and space" independent of the location, instance and server. When messages are sent and received i.e. the initiator and target are the same database, having multiple identical guids may not pose a problem. Unless you'd like to use the new service broker diagnostic tool called the ssbdiagnose Utility. See BOL for more detail: http://msdn.microsoft.com/en-us/library/bb934450(SQL.100).asp. SSBDiagnose is new in SQL Server 2008 however it can be used in SQL Server 2005 only environments or mixed. The utility will error when used against an instance where there are 2 or more databases having the same service broker guid. Yes, it is a problem even if the database being diagnosed has a guid that's not duplicated. The Service Broker Diagnostic Utility will generate output similar to:

Microsoft SQL Server 10.0.1442.23
Service Broker Diagnostic Utility
D 29997 MYSERVER\INST2 SSBReceiver1 Service Broker GUID is identical to that of database SSBReceiver on server MYSERVER\INST2
D 29997 MYSERVER\INST2 SSBReceiver2 Service Broker GUID is identical to that of database SSBReceiver on server MYSERVER\INST2
D 29905 MYSERVER\INST2 SSBReceiver Service Broker is not enabled in the database
3 Errors, 0 Warnings

To summarize, when making a copy of a database establish a new service broker identity. This will avoid any potential future conflicts and will guarantee that your service broker identity will truly be a G.U.I.D.!