Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Multiple routes to send messages to a TEST and a LIVE environment Expand / Collapse
Posted Monday, December 16, 2013 3:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 4:43 PM
Points: 57, Visits: 249
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?

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?

Post #1523453
Posted Thursday, June 19, 2014 8:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 21, 2016 5:59 AM
Points: 202, Visits: 776
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.
Post #1583868
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse