SQL Server Transactional Replication Over VPN

Question

I have transactional replication running between two servers over a dedicated VPN connection. The databases are fairly large, so I initially use the backup and restore method to get the initial snapshot over to the subscriber machine and then let it apply the incremental transactions from there.

Everything runs fine until the VPN line gets flaky (which it does occassionally) at which point the replication process is prone to locking up. When I look on the subscriber side, there are a few SQL processes which appear to be hung and have locks held on the subscriber database and tables. The crazy thing is that those processes are coming from the replication service. I can assure you (from trial and error) that no other processes are locking this database except for replication itself.

So why would the replication process trip over its own feet like that? Why would it get hung just because of a loss of network connectivity? Any suggestions for somehow making it more reliable?

Answer 1

I'll ask some questions and maybe they can give you some ideas as I don't have a clue here either.

Is there a way for the replicator to test for connectivity before attempting to start copying? Is there a way to put a connectivity test into whatever script you're using to perform replication? Is there a way to have the script bail in case of failure?

Answer 2

I have heard of issues like this over vpn connections. There is a post here that might help you.

Another option, if you have persistent problems, and depending on your requirements for speed and functionality, might be to use log shipping. In my humble opinion this can provide a more resilient way of moving data - at least from a networking perspective.

Answer 3

With SQL Server 2005 they allow you to replicate using a web service. This might not allow you to ditch the VPN but since web services are less connection driven that might help fix the problem. I haven't tried this myself so I don't know what the results may be.

As for the locks we've had a scare thinking alot of things were locked but it turned out that the replication monitor was just locking on its self so make sure you don't have that open when looking at the locks. That doesn't sound like your problem though.