Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple routes to send messages to a TEST and a LIVE environment


Multiple routes to send messages to a TEST and a LIVE environment

Author
Message
belgarion
belgarion
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 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?

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.
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search