[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 125
  • Last Modified:

Replication into cache and committed with 1 transaction; is this possible?

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.)
0
menreeq
Asked:
menreeq
  • 2
1 Solution
 
David ToddSenior DBACommented:
Hi,

You haven't said whether you are running transaction replication or snapshot replication. If you are completely rebuilding tables then snapshot replication may be more efficient.

Maybe try staggering the replications. ie break the publication(s) into smaller units,

Otherwise look at improving the pipe between here and there. Only real thing for it.

HTH
  David
0
 
menreeqAuthor Commented:
Hi David,
Transactional replication is implemented on a set schedule.

Currently I replicate a whole database of 10 tables or so per publication. When you mention staggering above, do you suggest to perhaps do one or two tables per publication?  This way I'm still dependent on publisher's VPN and internet connectivity, I'm trying to avoid it and I am afraid improving the pipe is beyond my control, since its client's bandwidth that I have no control over.
0
 
David ToddSenior DBACommented:
Hi,

Fairly easy really. If client is concerned about this issue, show the volume of data that is currently going down the pipe and point out that your timeouts require a fatter pipe to carry x data in y time. If client listens and improves pipe then you have succeeded. If client doesn't listen put it in writing and ignore the issue until client listens.

What is the business reason for the replication? What problem are you trying to solve?

Regards
  David
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now