I have 200 GB of SQL Server files I need to copy over a 100 Mbit connection. Assuming maximum throughput that will take five hours. SQL Server files compress very well, generally in the 80 - 90% range. Unfortunately the time it takes to compress and decompress the files on either end are a net zero in terms of time taken for the whole copy operation, mostly because of disk I/O limitations.

What I'm looking for is real-time compression and decompression during the network copy. rsync and scp have this, but that requires installing an ssh server on one of the boxes. I'd love to avoid doing that. Are there any handy tools out there for doing this without setting-up an ssh server?

I could probably whip up a C# program to do this by writing to a GZipStream over a network socket, but I don't like re-inventing the wheel.

  • How are you transferring the files over the network connection (i.e. what mechanism are you using to copy them). – EightBitTony Jun 20 '12 at 14:05
  • If I find nothing else I'll end up using robocopy or just standard Windows file copying. The database files will be detached, copied to the new server, then attached. There's no kind of fancy online replication or anything going on. This same problem applies to SQL Server backup files. Huge, but easily compressed. – mroach Jun 20 '12 at 14:07
  • You could already tell SQL Server to compress the files while backing them up. This would at least solve this problem. But I don't think you can compress them "on the fly" without installing anything on at least one of the boxes. – MichelZ Jun 20 '12 at 14:13
  • We use Deltacopy on our MSSQL backups. It is a windows implementation of rsync. It does a nice job of compressing and saving bandwidth by only sending parts of the files. – jqa Jun 21 '12 at 2:26
  • I found a good-enough solution. Using 7zip with multi-threading and the fastest compression setting, compression gets fast enough to make a substantial difference. This command uses 7zip with LZMA2 compression (which supports multi-threaded compression), fastest compression setting (-mx=1), and to use all 12 cores on the server (-mmt=12) for %f in (*.mdf, *.ldf, *.ndf) do "C:\Program Files\7-Zip\7z.exe" a -t7z -m0=LZMA2 -mx=1 -mmt=12 "c:\temp\compressed-sql-files\%f.7z" "%f" – mroach Jun 27 '12 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.