December 16, 2013 at 3:15 pm
Hi All,
We have the following V1 setup ... 6 LIVE DBs using triggers and SSSB to push updates into a highly optimised aggregated database that is the backbone of a web API.
Now enter phase two (V2) of development that introduces another 65 databases all pushing to the web API DB. V2 will be pushed into the live environment as soon as testing of the V1 functionality is complete. The V2 functionality will continue to be tested in a TEST environment.
Now here's the rub ... we need updates from the existing 6 DBs to be pushed to both the LIVE and the TEST environments. (The 65 LIVE DBs will push to the TEST web API DB so that's no problem.)
If we set up multiple routes in the 6 LIVE DBs - will a single SSSB message get sent to both targets?
Notes:
1. Messages are sent one way with the only "acknowledgement" being the close of the conversation so that SSSB cleans up the conversations
2. We can live with some manual "clean up" of SSSB internal tables if "acknowledgements" from the target responding 2nd (only to find the conversation is already closed) if we have too.
3. The 6 LIVE DBs have around 50 triggers all up so we're trying to avoid the coding in the "broadcast" pattern that's come in SQLS2012.
Anyone done this? Any alternative way to do this?
TIA.
June 19, 2014 at 8:40 am
I'm not sure if Service Broker is the appropriate tool to "copy" that much databases. Maybe you should think about replication, in your case it might be transactional replication.
The only thing to bear in mind: replication does not transfer statements transactional. But maybe you did not care of this in your Service Broker procedures, you can check that.
If transactional consistency is not a big matter on your destination databases think of using replication.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply