Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 2 '11 at 1:06

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.