0

I have a well-connected remote site that I'm delivering snapshots to twice-daily of certain tables. Snapshot replication is the only method that I can use based on the schema limitations of the underlying application.

The snapshot is published via UNC path and FTP, and is about 6MB so fairly small. I'm not 100% whether FTP or UNC is being used actually. It takes about 5mins from the time the snapshot is compressed into the snapshot.cab file to when the replication monitor shows me the first of the scripts being applied, so I assume that gap is transfer time. I'm thinking its UNC/SMB given that it takes a lot less time to copy that much data over our 3MB connection via FTP.

It takes about 25 more minutes to load the snapshot, which seems like a lot of time for a modest amount of data. Lots of rows (several tables have 100K rows), but the actual amount of data is pretty small.

The subscriber is running SQLExpress 2005 on a 2.8GHz ProLiant with 3GB RAM that's otherwise not busy. I haven't done extensive profiling yet to see if there's disk contention or what else might be going on.

I'm wondering what the likely rate-limiter is here and if there's a way to speed this up at all. 30mins seems like a long time to copy and load 6MB of compressed data.

Brian Spolarich
  • 227
  • 1
  • 4
  • 12

1 Answers1

0

Since you have SQL Express on the remote side your options are pretty limited as you can only do a push subscription.

The way replication works is that the data is BCPed to a binary file. When the replication agent picks up that the snapshot is done, it uses BCP to load to data over the network into the subscriber. BCP over the network isn't exactly the fastest process ever.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • I assumed that since the snapshot was compressed into a snapshot.cab file and FTPed (I see in the logs now that the FTP method was used to deliver the snapshot) that the BCP was happening on the subscriber off of the locally-expanded files from snapshot.cab. It seems like most of the time is spent (25mins or so) loading the BCP files. Given that I'm limited to snapshot publication because of my application, what could I do? Would putting a real copy of MSSQL out there help? – Brian Spolarich Jul 09 '09 at 14:02
  • If you had the Workgroup or higher edition then you could run the replication agent on the subscriber. This would allow you to download the files to the subscriber and run the import locally. As it is now the files are uploaded to the FTP server from the publisher, then downloaded to the distributor (probably the same machine) and then BCPed into the subscriber over the network. – mrdenny Jul 09 '09 at 19:57
  • Sound like that's the difference. The docs don't really give you a good idea about the implications of running the agent on the publisher vs. the subscriber. Well I'll leave it alone for now, and when the latency becomes an issue I'll put workgroup over there. Thanks! – Brian Spolarich Jul 10 '09 at 14:17