Sql-server – Find which databases have a particular service in SQL Server Service Broker

service-brokersql server

From what I currently understand, the names of services are global to the instance and you can refer to them without knowing which database contains the service and associated queue etc. In addition, you can have the same service in different databases on the same instance and in that case, you should use routing and can also use the service_broker_guid to distinguish them when you BEGIN DIALOG CONVERSATION.

Is there a better way than looking in all databases to see what databases the service name exists in?

The sys.services catalog view is restricted to within a particular database context only, either the current database or it can be called with a fully qualified database_name.sys.services.

In particular, I have some triggers that need to send messages to a service in another database, but I don't want them to fail if the service is not there, or if there are two databases with the service, or the database name is different etc.

If performance is too slow to do all these safety checks in the trigger every time, I can do regular maintenance checks to drop them, but whether the trigger is there all the time or not, I still would like the best way to know if the service is defined in the instance at all in one or more databases.

I did manage to come up with this dynamic SQL, but that would probably have to go in a proc or something:

USE master;
DECLARE @DatabasePattern AS varchar(max) = '%';
DECLARE @ServicePattern AS varchar(max) = '%';
DECLARE @SQLTemplate AS varchar(max) = 'UNION ALL
SELECT database_id = {database_id}
    , database_name = ''{database_name}''
    , service_name = s.[name]
    , service_broker_guid = ''{service_broker_guid}''
FROM {database_name}.sys.services s
WHERE s.[name] LIKE ''' + REPLACE(@ServicePattern, '''', '''''') + '''
';
DECLARE @SQL AS varchar(max);

WITH Statements AS (
    SELECT SQL = REPLACE(REPLACE(REPLACE(@SQLTemplate
                    ,'{database_id}', db.[database_id])
                    ,'{database_name}', REPLACE(db.[name], '''', ''''''))
                    ,'{service_broker_guid}', db.[service_broker_guid])
    FROM sys.databases db
    WHERE db.[is_broker_enabled] = 1
        AND db.[name] LIKE @DatabasePattern
)
SELECT @SQL = STUFF((SELECT SQL
FROM Statements
FOR XML PATH (''), TYPE).value('.', 'varchar(max)'), 1, LEN('UNION ALL'), '');

EXEC (@SQL);

Best Answer

you can have the same service in different databases on the same instance and in that case, you should use the service_broker_guid to distinguish them when you BEGIN DIALOG CONVERSATION

No. Typically you should let Service Broker Routing to find the correct target service for the new conversation. If you have multiple services with the same name on a SQL Instance, the default behavior is to round-robin new conversations across the matching broker instances. If you want all the conversations initiated by from a database targeting a service b to route to a specific database, then add a route in the initiating database specifying the broker_id of the target database. EG

CREATE ROUTE b_route  
    WITH   
    SERVICE_NAME = 'b',  
    BROKER_INSTANCE = 'EF782150-7F7B-4544-937E-B03CF2A3248C',  
    ADDRESS = 'LOCAL' ;

This allows you to specify the routing at deployment time, and your service broker code does not need to specify the target broker instance.

Related Question