March 4, 2008 at 2:21 am
Please help. This problem just won't go away. I'm trying to set up a merge replication between two 2k3 servers using SQL Server 2000. The publisher is in London and the Subscriber, connected via vpn, is in Lahore, Pakistan. Currently there is about 15Gb of data to shunt between the two, and I just can't get it to go. It will go along quite happily for several hours and then, at different points stops with the error, "The process could not deliver the snapshot to the Subscriber", and sometimes "The process could not bulk copy into table '"dbo"."tablename". This causes sql to start from the beginning again.
I've tried breaking the data down into several publications. Some have succeeded and others have repeatedly failed.
I've tried backing up the data in London, restoring in Lahore and telling SQL not to create the data, but I can't get this to work either.
I've put the database into single user mode and run a DBCC CHECKDB, and no errors have been found
I've recreated the publication from scratch several times
The routers are telling me that there has been a solid connection between the locations
Is there any other method I can use to get the data across? Am I missing something really obvious?
I've run out of ideas and my boss is running out of patience. Please Help
Any suggestion would be most gratefully received
March 4, 2008 at 9:44 am
Your snapshot may run at peak houts. You may try to change the schedule of your snapshot. May it work.
March 5, 2008 at 6:35 am
I have seen this problem many times myself with Transactional Replication. We have one VPN link with limited bandwidth that we replicate over. When we replicate the full database, we will always see errors during the push of the initial snapshot, and then the snapshot push has to start over.
What we have done is dropped all the large tables out of the publication (in our case there are 12 tables that consist of about 75% of the size of the snapshot). So we are able to sucessfully replicate the 700+ smaller tables.
Once the initial snapshot is complete, we add the large tables in one by one (as subsequent snapshots that we generate will only include that one table).
However, this is Transactional Replication and not Merge, so I don't know if the same rules apply.
Either way, that's obviously not the best solution and I would love to know a way to put the Distribution Agent into error recovery mode rather than bail out and start from scratch mode. It just doesn't make any sense to me that the Distribution Agent could apply all the setup scripts, and start bulk copying data into tables and then when it encounters an error it has to start from scratch. I would think that at most it would have to start over on that table (or that specific block for that table), but I would think it should know exactly what was sent and where it failed and thus pick up right back where it left off.
Anyone that can provide some insight into any available advanced replication configurations that exist to resolve this issue would receive my eternal gratitude.
March 6, 2008 at 9:38 am
Is your snapshot compressed?
Using the wizard, you cannot do this, but once you have created the publication, you can edit it and mark it to be compressed. This may help.
The other thing I have done is backup the database, compress it, and send it as a whole out to the remote location during off peak hours and restore it, then sync the 2 without a snapshot. I am replicating from Cleveland to Seattle with only a half T1 between them, with multiple merge and transactional subscriptions. I have had to tweak the merge agents to use smaller batches and allow 20 minutes to complete so it won't scare me with warnings. I have had success breaking the publications into multiple publications with smaller amounts of data in each. Sync them up one at a time, and do not start the next one until the prior has completed applying the snapshot.
March 6, 2008 at 10:05 am
Mark Bugner (3/6/2008)
Is your snapshot compressed?Using the wizard, you cannot do this, but once you have created the publication, you can edit it and mark it to be compressed. This may help.
Hey Mark,
No, the snapshot is not compressed. That would probably help us greatly! I'll look into that now so we can try that the next time we need to reinitialize replication.
Looks like all I have to change is the sp_addpublication option "@compress_snapshot" from false to true the next time I create the publication, correct (we have scripts for setting up the publications and subscriptions)?
Thanks!
March 6, 2008 at 12:04 pm
Correct! Let me know how it works, I can share some settings I have for the merge agents if you need more assistance. If you data doesn't change on a large scale as much as mine does, you may not have any problems with it. Another property you might want to change to speed along applying the snapshot is @retention and @retention_period_unit of the publication. Keep the retention period of the metadata low, yet conservatively high enough. 14 days is way too long in my mind. I have mine set at 2 days for the troublesome subscriptions.
Also, be sure merge agent profile has MetadataRetentionCleanup set to 1 (the default).
March 28, 2008 at 5:21 am
Sorry for the delay in my response, it's taken me some time to get the resources together to test this out.
Unfortunately, while compression would have helped tremendously, we are doing push subscriptions (so our local distributor unpacks the snapshot to apply it to the remote server) so this won't help our situation. Due to security reasons we can not convert to pull subscriptions.
September 30, 2008 at 10:27 am
Hi Grasshopper
Have you figured out a way to achieve this.
I am also into same situation
October 17, 2008 at 6:26 am
After applying SQL 2005 CU8 to our Distributor, the Distribution Agent now recovers from failures during the initial snapshot push . It will still "start over" but it now recognizes what has already been completed (with a message like "Skipping script/bcp file - already delivered in a previously failed attempt")
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply