I have transactional replication running between two servers over a dedicated VPN connection. The databases are quite large, so I initially use the backup and restore method to get the initial snapshot to the subscriber unit, and then allow it to apply incremental transactions from there.
Everything works fine until the VPN line is peeled off (which sometimes happens), at which point the replication process is subject to blocking. When I look at the subscriber side, there are several SQL processes that seem to freeze and have locks stored in the database and subscribers tables. The crazy thing is that these processes come from the replication service. I can assure you (from trial and error) that no other processes block this database except replication itself.
So why would the replication process move to its own feet? Why would it damage just because of a loss of network connectivity? Any suggestions to make it more reliable?
source
share