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.

I would like to set a SQL broker service whereby DatabaseA sends the same data to DatabaseB and DatabaseC. In this case, all databases reside on the same instance.

I have looked at the following examples, however, they only show me how to setup a SQL broker service between two databases:

I would be grateful if anyone could provide any assistance on this.

share|improve this question
    
Why do you want to use service broker ? If you want to send/replicate data then better use Transactional replication. –  Kin Aug 16 '13 at 12:11
    
Thanks for the suggestion Kin - I have not used "Transactional replication" before. I am keen to learn - is there a tutorial you'd recommend? –  Joe Bloggs Aug 16 '13 at 15:28
add comment

migrated from stackoverflow.com Aug 16 '13 at 11:34

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

3 Answers

up vote 1 down vote accepted

In your code the ReceiveOrderForProcess procedure in ReceiverDB2 should be looking at a queue called DestQueue2 rather than DestQueue. I think.

share|improve this answer
    
Thank you Rikalous!! YOU ROCK!! –  Joe Bloggs Aug 16 '13 at 15:25
add comment

You can create 2 services, one for each destination database, and send the same message to both.

I suppose you could daisy-chain instead, i.e. A sends message to B which then processes it and forwards it to C, but I expect the 2 service way is simpler.

share|improve this answer
    
Thanks you for your response. I have unsuccessfully tried the "create 2 services, one for each destination database" approach. I agree with you, that it would be best option. Please see my code below. [create 2 services, one for each destination database][1] [1]: sdrv.ms/144NpyG Thanks. Carl 02031943406 –  Joe Bloggs Aug 16 '13 at 13:38
    
How does it fail? Does one service work or neither? –  Rikalous Aug 16 '13 at 14:14
    
Yes the first service works and the second does not. –  Joe Bloggs Aug 16 '13 at 14:23
add comment

DatabaseA sends the same data to DatabaseB and DatabaseC.

I would not recommend using service broker for this scenario. Transactional Replicaiton is the best option.

It even has the ability to filter only required tables, columns, etc as per your need.

T-Rep is asynchronous and since it is the same server, there would be near to zero latency.

Out of Curiosity: what is the purpose of sending same data to 2 other databases on the same instance ?

Reference: Stairway to SQL Server Replication

share|improve this answer
add comment

Your Answer

 
discard

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

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