aero1


I am using SQL2005 merge replication and have pull subscribers on a low bandwidth link
I am compressing the snapshot into an alternate folder. Files are not put into the default folder
When I start a synchronization, I would expect the cab file to be copied to the subscriber and then the files to be extracted locally at the subscriber in order to apply the snapshot
However, what appears to be happening is that the files are being extracted from the cab file on the publisher (in a UNC specified directory) and then copied in their uncompressed form to the subscriber - resulting in an extremely slow snapshot application.
Any ideas what I am doing wrong I have read about the options for using FTP to transfer snapshot files, but I am not clear whether I have to use FTP in order to transmit a compressed snapshot. I don't want to use FTP unless I need to.
Any suggestions would be much appreciated
aero1



Re: Transmission of compressed snapshots

Tom Michaels


Compressed snapshot files are supposed to be decompressed where the merge agent is executed. This means that if you are using a push agent (which by default executes at the distributor), the cab file would be decompressed at the distributor followed by the data and files being sent over the wire to the subscriber. If you are using a pull agent (which by default executes at the subscriber), the cab file should be copied to the subscriber and then the data and files decompressed at the subscriber.

It seems like what you describe indicates that the merge agent is getting executed at the publisher/distributor Could you please double check where the merge agent is getting executed at.

Thanks,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.







Re: Transmission of compressed snapshots

aero1

Please note that the pull subscribers are running SQL Express ¨C so I havenĄ¯t been using SQL Agent to invoke synchronization

I have synchronized using Windows synchronization manager and using RMO (MergeSychronizationAgent.Synchronize)

In both cases, if I do an sp_who2 when synch is running, I see the merge agent process running with the hostname of the subscriber. (Although I donĄ¯t see the merge agent running in Task manager) . Did you have any particular method in mind to check where the merge agent is running Note that I setup the subscription using the GUI ¨C specifying that the merge agent should run on the subscriber.

The only other thing I am planning to test is to directly invoke replmerg.exe from the command prompt to check the behaviour.

I do now have a potential workaround for this problem. I have a DLL which will invoke the synchronization. If the subscription is inactive then I can copy the snapshot to the subscriber prior to invoking synchronization. The subscriber can be setup to access the snapshot from the local location. I canĄ¯t see any problems with this approach, but I would rather not put in extra steps if they really arenĄ¯t needed

Thanks for the feedback

aero1






Re: Transmission of compressed snapshots

aero1

I have now run a test invoking replmerg from the command line on the subscriber - and I am seeing the same behaviour. i.e. files are being extracted from the remote cab file before transmission over the wire



Re: Transmission of compressed snapshots

Raymond Mak

Unless you are using websync, the uncompressed files should be saved locally on the subscriber machine (default temp directory) and applied from there. So theoretically only compressed bytes should be transmitted over the network from the remote cab file. I am probably missing something along the line...

-Raymond





Re: Transmission of compressed snapshots

aero1

I am not using websync

It might help if I explain how I am deducing that the uncompressed data is being transmitted

If I copy the snapshot to the subscriber - it takes about 3 minutes over the link

When the subscriber is configured to accesss the local copy of the snapshot, snapshot application takes less than 5 minutes

If I don't copy the snapshot to the subscriber - so the subscriber is looking for the snapshot on the UNC specified directory on the remote server, then I see messages about files being extracted and applied quite soon (first messages in Windows Sync manager within about 30 seconds), but there is a delay between each script application - which results in a total time to apply the snapshot being in excess of 7 hours. It seemed a reasonable assumption to me that this was because uncompressed bytes were being transferred.

I would have expected the merge agent to copy the cab file to a temp directory on the subscriber prior to extracting and applying the scripts - but I can see no evidence of this happening. Is this what should happen - or am I expected to do the copy of the cab to the subscriber myself

aero1





Re: Transmission of compressed snapshots

Raymond Mak

First of all, thanks for taking the time explaining your situation.

Unfortunately this seems to be a case where our (or specifically my) expectation doesn't match well with reality. In theory, copying the cab file to the temp directory will incur higher latency in the snapshot delivery process versus simply extracting the cabinet over the network. After all, more of less the same number of compressed bytes should be transmitted over the network in both cases but extracting the cabinet over the network has the advantage of not having to spool another copy of the cabinet file to the local disk before extracting the snapshot files. Clearly, this assumption has proven to be a gross error in your case and I am deeply sorry about that. However, given my (admittedly incomplete) understanding of how various pieces of the system work, I have my doubt that the slow snapshot transfer performance has to do with wholesale transfer of uncompressed snapshot data over the network. Instead, I have a vague suspicion that the access pattern of the cabinet extraction API is suspectible to very poor performance over high-latency network as it may require many network round-trips for many random accesses to the cabinet file. It would be really helpful to us if you can monitor the network bandwidth utilization when you try to apply the cabinet over the network from a UNC share. If there is a high utilization of the network link then it is likely I am holding another wrong assumption again and I need to dig deeper into this. Otherwise, it may be the case that high network latency is the culprit and you may want to check into that.

Regardless of the outcome of your further investigations (if you are so inclined), I have to agree that what replication offers in terms of compressed snapshot tramission is completely inadequate in your case. As you mentioned before, confguring FTP for snapshot data transfer will be one option that you cannot implement (this works because we have no choice but to download the cab file before extracting files from it). Other than that, your only recourse will be copying the cab file manually as you have been doing. As to what we can do to fix this, it would not be wise for us to always make a copy of the cab file locally as that may negatively affect performance in some cases (push subscriptions for example). I can, however, envision implementing a trace-flag in the merge\distribution agent to copy the cabinet file to the local temp directory so folks with situations similar to yours can make the appropriate trade-offs. If this is an urgent matter, you will need to contact Microsoft Customer Support for a post SP2 QFE. Otherwise, you can log a feedback item at http://connect.microsoft.com/SQLServer although we probably cannot do anything about that until SP3 at the earliest.

-Raymond





Re: Transmission of compressed snapshots

aero1

Raymond - thanks for the information

I now have enough information to conclude that a separate copy of the snapshot is the best option in my scenario

I will log a feedback item on the connect site as you suggest.

I don't have any easy way at the moment to measure the bandwidth utilisation - but I may be able to come back to that at some time in the future

Thanks again for your help

aero1