There are 200 transactional DBs repesenting 200 stores that need to update a table in a remote Central database. Central database needs to have push capabilities as well.
1. given that the 200 DBs = source and Central DB = target, Is a separate Message type, Contract, Queue, Service, and Endpoint needed from each source?
2. When the Central DB needs to push data back, which objects can it utilize from #1 above?
I would like to involve someone familiar with this issue to have a look and give an update later. Thanks for your understanding.TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.
TechNet Community Support
Each initiator (source) and target will have a common message type and contract. So these would be the same on all servers. Each sever (initiator and target) will also have to have an endpoint configured but this can be the same definition. Remember you will have to open up the ports if there are firewalls between the servers. You can check this by telneting both ways successfully. You can also have the same queue name on each server. However I would make each service name unique. You might append the storeId or name to the service name to make it unique.
You also need to consider the routes. The initiators will have to have a route to the target. So each initiator will have one route. On the target you will have to have a route to each initiator. The route takes the service name and address (host name or IP address). You are looking at 200 routes on the target. Routes are needed on both the initiator and target inorder for the message to be delivered and for the ACK to get back to the initiator signalling that the message was delivered successfully.
You might take a look at the TRANSPORT option for "create route" to see if this is an option for your situation. This would cut down on the number of routes to manage.
When the target needs to "push data back" it can use the same objects if you want or new objects. I would probably create a new message type for this an just add it to the existing contract.
You can create activation stored procedures that are associated with a queue. When a message arrives in the queue the stored procedure is automatically executed. In the activation stored procedure it will receive the message from the queue and check the message type in an if or switch statement and process accordingly (like parse the xml message body and insert or update a table).
Also remember that you will have to end the converstion on both sides when you are done with them. So thinking about the life time of a conversation instead of a message is helpful.
The Service broker development teams blog site has a lot of good posts and sample code. http://blogs.msdn.com/b/sql_service_broker/ You might want to review "Using multiple routes in service broker" and "Reusing dialog pools". There are also examples for service broker on codeplex. http://msftsbprodsamples.codeplex.com/
Hope this helps,
Bill -- Microsoft CSS
- Proposed as answer by Eric WisdahlModerator Friday, February 17, 2012 4:52 PM