Publisher: 1.5 GB of size. Push merge subscription.256 K full duplex internet connection.
Subscriber: physically at different country. T1 connection. I dial VPN to connect to the publisher which is in seperate domain. Since the domains are different, i am using SQL authentication for my replication process.
The problem with the snapshot is "disconnectivity". Merge agent starts form beginning after running for 2 or 3 hours. I have following entries for MSrepl_errors table in the distribution database:
error_code -2147201001 "The process could not deliver the snapshot to the Subscriber"
error_code 20037 "The process could not bulk copy into table '"dbo"."MY_TABLE"'"
error_code 11 "General network error. Check your network documentation."
The thing that bothers me much is that the VPN scarcely disconnects, only a single packet drop forces the merge agent to retry!!!. To isolate the problem i started ping reqest from publisher to subscriber; i saw merge agent to fail when a packet loss occured for only one second. VPN did not disconnect.
Currently i've chosen "slow link" profile for merge agent.
I started the snapshot 2 days ago and it restarts after every 2 or 3 hours.
Why the heck merge agent starts from the beginning; couldn't it be handled to start where it was when the connection was lost? Packet-loss is normal on the internet connections. I would love to know how u people replicate VLDBs over the internet.
I tried dynamic snapshot but found it rather risky; the merge agent stops with success during the middle of snapshot if connection is dropped leaving the subscriber with inconsistent data.
Any ideas and tips would be very much appreciated.
An awful lot depends on what got lost, and where it got lost in the process. Merge replication is also a hodge-podge kind of thing, it is usually "interesting" trying to figure out where the "beginning" of it is, since it scarcely differentiates the difference between tables, much less any order to their changes.
Connections are definitely not "parts is parts" around the world. A connection from one part of the US to another is frequently "rock solid". A connection from here to London is very nearly as good, as is one from London to Sydney. One from the United States to Sydney is absolutely awful. So, we move the data going to Sydney through London... Even though it ends up going most of the way around the world instead of only about one fourth of the way, it is still ots more reliable.
Unless you have lots of time, energy, and money, this is where I'd bring in the "hired guns" to solve the problem. If you bring in someone like Microsoft Professional Services that deals with the various IP providers, knows the problems that exist between point A and point B, and understands the way that your specific replication setup is configured, you'll get an answer much faster and in the long run cheaper than trying to find the solution yourself.
Hi, Pat. Thanx for ur feedback.
I've tried this thing.
Publisher name SERVER1
Subscriber name SERVER2
I uploaded the SNAPSHOT.CAB (300 MB) from the publisher using download manager (if connection disconnects, it resumes where the connection was lost). It got uploaded in 3 hours.
Now i dropped the push merge subscription. Created Pull merge subscription at SERVER2. Placed the SNAPSHOT.CAB into local C drive of SERVER2.
The location i've extracted the snapshot file is:
In the Snapshot Location dialogbox for pull merge agent, I specified this location to force the agent to pick snapshot locally.
When i run the merge agent; it fails, for it searches the snapshot files from the default location at the publisher rather than the one i specified . The error is:
The schema script '\\SERVER1\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\unc\SERVER1_MBDB_MBDB_PUBLIC ATION\20040619233252\UR_TABLE_1.sch' could not be propagated to the subscriber.
Could not find the cabinet file 'snapshot.cab' in folder 'c:\unc\SERVER1_MBDB_MBDB_PUBLICATION\200406192332 52\unc\SERVER1_MBDB_MBDB_PUBLICATION\2004061923325 2\'
How can i square this location problem.
If you are using a Merge PULL subscription, check the subscription properties at the subscriber to be sure that they specify the correct directory.
I'm a bit nervous about making suggestions, since you are making some pretty significant changes to what you are doing (push versus pull, where the agent exectutes, etc). Be careful to think out what you are doing!
Hi, thanx again 4 ur reply.
I did specify the correct directory where i placed the snapshot files. But the merge agent tries to access the snapshot from publisher's default directory rather than the local path.
I am tryin to use pull instead of push, for push won't work in my case due to disconnection. However i could move the whole snapshot.cab file to subscriber using a download manager despite the connection is lost b/w the transfer. But it isn't working either here.
I seem to find how to solve this problem: the point here is that SQL agent is running by default under system or network account. You must specify this account when you install the MSSQL server. This works fine until the agent needs to access some network path. So I went to SQL configuration tool and changed the account under which SQL Agent is running to my own user name. I ensured that I can access the network file with my user name from Windows explorer and it solved the problem.