Challenging question about using replication databases to refresh a test database that's also being replicated

• pharmkittie

  SSCertifiable

  Points: 5775

  More actions

  February 19, 2013 at 3:21 pm

  #394221

  Hello,

  I have two servers that are involved in this scenario: One prod (call it Megalon) and one test (call it Godzilla). The database I want to refresh (let's call it Ben) exists on both Megalon and Godzilla. On both servers it's already acting as a publisher and distributor). On Megalon it replicates (transactional replication of a subset of the tables only, no other objects) to a third server (call it Hedorah and the database it replicates to is called Ben_Repl)). Godzilla also replicates to itself so the Ben database replicates to Ben_Repl database, both on Godzilla. All this exists for current production and testing needs. The new component that I need to set up:

  I need a way to set up a weekly job to refresh Godzilla's Ben database with Megalon's Ben database data. How can I do this with the existing (all transactional) replications in place?

  Can I do it with snapshot replication?

  Can I do it with SSIS?

  Can I do it with something else?

  The trick is how do I keep the existing replications functioning and the identity values set to their appropriate values? I am new to transactional replication and I have never had to deal with a situation like this.

  I've been trying to find an answer to this for a long time without success. I'd really appreciate any and all insights and suggestions.

  Please let me know if I need to clarify.

  Thanks!