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?
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?