6

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);
1

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.

  • OK, I will add a comment about routing. But that still doesn't answer the question about discovering the configuration separate from sending messages. – Cade Roux May 22 '19 at 14:04
  • But you only need to discover the existing services and broker id's at deployment-time. Never at runtime. So in the unusual case that you don't know what services are deployed, you can run a query that looks in all the databases. – David Browne - Microsoft May 22 '19 at 14:14
  • So what techniques might you recommend if we were looking at a trigger-based system to send data from the transactional system to an optional add-on analytics module via Service Broker? This is in a turn-key system. So triggers are there if and only if the service is installed and if the service is disabled/removed, the triggers are also removed? – Cade Roux May 22 '19 at 17:00
  • I would probably just deploy the trigggers and have them disabled by default. Or install the triggers when deploying the analytics service. – David Browne - Microsoft May 22 '19 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.