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.
migrated from stackoverflow.com Dec 2 '11 at 1:06
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
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
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).