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.
In your code the ReceiveOrderForProcess procedure in ReceiverDB2 should be looking at a queue called DestQueue2 rather than DestQueue. I think.
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.
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
