0

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.

2
  • Why do you want to use service broker ? If you want to send/replicate data then better use Transactional replication.
    – Kin Shah
    Aug 16, 2013 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, 2013 at 15:28

3 Answers 3

1

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

0
0

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.

3
  • 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, 2013 at 13:38
  • How does it fail? Does one service work or neither?
    – Rikalous
    Aug 16, 2013 at 14:14
  • Yes the first service works and the second does not.
    – Joe Bloggs
    Aug 16, 2013 at 14:23
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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