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.