SQL Server transactional replication for very large tables

Question

I have set up transactional replication between two SQL Servers on different ends of a relatively slow VPN connection. The setup is your standard "load snapshot immediately" kind of thing where the first thing it does after initializing the subscription is to drop and recreate all tables on the subscriber side and then start doing a BCP of all the data. The problem is that there are a few tables with several million rows in them, and the process either a) takes a REALLY long time or b) just flat out fails. The messages I keep getting when I look in Replication Monitor are:

• The process is running and is waiting for a response from the server.
• Query timeout expired
• Initializing

It then tries to restart the bulk loading process (skipping any BCP files that it has already loaded).

I am currently stuck where it just keeps doing this over and over again. It's been running for a couple days now.

My questions are:

1. Is there something I could do to improve this situation given that the network connection is so slow? Maybe some setting or something? I don't mind waiting a long time as long as the process doesn't keep timing out.

2. Is there a better way to do this? Perhaps make a backup, zip it, copy it over and then restore? If so, how would the replication process know where to pick up when it starts applying the transactions, since updates will be occurring between the time I make the backup and get it restored and running on the other side.

Answer 1

Yes. You can apply the initial snapshot manually.

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

Edit: From BOL How-tos, Initialize a Transactional Subscriber from a Backup

Answer 2

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

I think you can easily figure the potential speed gain by comparing sizes of standard and compacted snapshots.

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