Clever Geek Handbook

SQL Server Transactional Replication for Very Large Tables

I have set up transactional replication between two SQL servers at opposite ends of a relatively slow VPN connection. The setup is your standard "download snapshot" where the first thing it does after initializing the subscription is to dump and recreate all tables on the subscriber side, and then start BCP execution of all data. The problem is that they have multiple tables with a few million rows, and the process either a) takes a REALLY long time or b) just a flat error crashes. Messages that I receive when I look in the Replication Monitor:

  • The process is running and waiting for a response from the server.
  • Request timed out
  • Initialization

It then tries to reload the bulk upload process (skipping any BCP files it has already downloaded).

Currently I am stuck where it just keeps doing it over and over. It has been working for a couple of days.

My questions:

  • Given that the network connection is so slow, is there something I could do to improve this situation? Maybe some tweaks or something else? I don't mind waiting a long time for the process to take time away.

  • Is there a better way to do this? Perhaps make a backup, zip it up, copy it and then restore it? If so, how will the replication process know where to get it, when it will start applying transactions, since updates will occur between the time I create a backup and restore it and start it on the other side.

+1


source to share


2 answers


Yes. You can manually apply the original snapshot manually .

It's been a while for me, but the link (in BOL) has alternatives to setting up a subscriber.



Edit: from BOL How-tos, Initialize Transactional Subscriber from backup

+3


source


In SQL 2005, you have a "compact snapshot" option that reduces the overall size of the snapshot. When applied over a network, the snapshot items "travel" are compacted to the subscriber, where they are then expanded.

I think you can easily understand the potential increase in speed by comparing the sizes of standard and compressed images.



By the way, there is a (rather) similar question here for merge replication, but I think there is no difference at the snapshot level.

+1


source






More articles:


All Articles