Accessing Service Broker from Different Databases

Question

In my Application we have a lot of databases nearly 100 dbs. We are planning to use Service Broker for calling a Stored Proc Async. Instead of running Service Broker on all the Databases, Is it possible to run the Service Broker on only one Database and all the databases can share the Service Broker. I appreciate your guidance. I am new to Service Broker and I apprecieate if you show me some example how to implement it.

Answer 1

Consider the Asynchronous Procedure Execution example. One approach is to simply have one DB hosting all the SSB objects involved (the services, the procedure, the activation queue etc) and simply call the invocation procedure usp_AsyncExecInvoke across DBs, eg. EXEC SSBExecutionDB.dbo.usp_AsyncExecInvoke .... The SSB activated procedure could in turn run the code in the DB you invoke it from, by simply passing in a fully qualified 3 part name to execute:

EXEC SSBExecutionDB.dbo.usp_AsyncExecInvoke 'MyDb.dbo.MyProcedure';

Note that your procedure will execute under activation EXECUTE AS context and will thus be subject to the EXECUTE AS sandboxing. You must either mark the SSBExecutionDB as TRUSTWORTHY, or use code signing, see Signing an Activated Procedure.

This approach keeps all the SSB infrastructure in one place (in one DB) so it may be feasible for you. The disadvantage is that it does not leverage the loosely coupled SSB model which would allow you to schedule the async procedure even when the executionDB is not available. It also introduces a tight coupling between your DBs and the execution SSB DB, which creates problems vis-a-vis restores and failover (mirroring).