Replication into cache and committed with 1 transaction; is this possible?
Posted on 2014-08-26
I’m using Sql Server2008R2. I’m replicating two databases over VPN from one server to another (2008R2). I'm using push replication where the publisher is also the distributor. Transactional replication is implemented on a set schedule, and that's because ETL packages run and pull tables beforehand (to be replicated after) from many locations. These ETL packages truncate old tables and recreate them (updated) on every run. (Hence, we have to run replication on a set schedule or empty tables may get replicated.) Furthermore, I am wondering whether our replication can be done to first cache replicated changes on the subscriber side and then commit everything at once (at subscriber.) Sometimes replication drags out due to poor VPN or internet bandwidth or whatever else, and I’d like to be able to control process interlocks to some extent.
There are sql jobs and other things after replication comes through on the subscriber side, and interlocking is a problem when we push replication to the limit (to be as frequent as possible.)