file transfer ideas...
-
We are researching alternatives to our currently method of transferring a bak file from our production database server to our staging database server. The databases contain sensitive data and we are trying to work with PCI compliant regulations.
Do any of you have suggestions on tools or software to use or NOT use? Our current sftp method takes about 5.5 hours, which is not acceptable. The file size is roughly 37GB.
I am also looking into splitting the bak file into multiple files and compression...if you have any insight on that, feel free to share as well! 🙂
Thank you for your time!
-
Use a backup compression utility that will also create encrypted backup files. I know that LiteSpeed will do this, and I believe Redgate backup will do it also.
This will make the backup files much smaller and cut your transfer time as well as protect the backups from anyone who does not know the password.
-
ZipVDI tool meets all your requirements, but it is not as friendly as commercial tools. You can try ZipVDI to get an idea of what you can achieve with commercial tools. If you get satisfied you can either continue with this tool or probably better buy a supported tool.
regards
-
We use robocopy for many filecopy operations. It works well across the WAN, and is pretty robust. I understand it's easy to automate as well. It's available from Microsoft.
-
Well, you have a couple of issues, but they all boil down to a combination of:
The size of the data (which you gave).
The bottleneck of the lowest speed portion of the links between the servers (which you didn't mention anything about other than the actual speed).
37GB * 1024MB/GB = 37888 MB
5.5HR * 3600SEC/HR = 19800 SEC
37888MB/19800SEC ~= 1.91 MB/SEC
Therefore, at this time, if we assume your network links are fairly optimized, I'd guess somewhere on the path that data takes is a small set of teamed/bonded 10Mbit Ethernet connections... or one of the SFTP machines is bottlenecked (the first guesses: the kind of chip it's using for a CPU is a Dorito, or it's thrashing). Or, perhaps a network device is throttling data along the path (smart/managed switch at the port level, or a firewall).
Doing compressed backups with SQL Server 2008 Enterprise, RedGate, HyperBak, Litespeed, if it cuts down one or all of size, backup duration, and restore duration, will certainly help, and will help no matter what else you do (assuming your system can handle it well, which any modern system should).
Upgrade to 100Mbps, 1Gbps, or 10Gbps links :). If you go/are Gbps or higher, you can also investigate jumbo frames TCPReceiveWindow and window scaling. DO NOT implement any of these casually or without some benchmarking, but do investigate them.
The truly advanced data transfer answers tend to involve SAN storage that can "Snap" from one machine to the other, so the backup goes to the LUN, then the LUN is "Snap"ed to the machine for the restore, then the restore comes from the same LUN. These, unfortunately, can be quite finicky and prone to failure; consult your SAN vendor.
One way to view current values as seen on one site on the internet (note that other devices in between may have changed those values):
http://www.speedguide.net/analyzer.php
Note that Richcopy is superior to Robocopy in that it does multi-threaded copies, so if you split your backup into multiple files, depending on how your network is set up, you may see a throughput increase by the multithreading.
Very quick but silly option: Buy an additional hot swap drive, external drive or external portable RAID setup (USB < eSATA ~= SCSI ~= SAS ~= hot swap drive), and use sneakernet. Even at 30MB/sec for USB on the copy and the restore, plus 30 minutes of sneakernet time, you'd still be way ahead of the current speed.
-
Another tool for you to consider..Using WinRAR to compress the .bak file on the source side ,
then robocopy the compressed file to the destination,
and then decompress from the destination.
The compression usually could cut the size of the file A LOT.
You can try to compress it manually on your source file and see how it performs.
If you are satisfied with the compression rate, you could go for the following:
1. Install WINRAR (free) on both servers.
2. Set up a job (or add a job step after your backup step in your backup job) to compress your backup file on Source server:
DECLARE @filename varchar(200),
@cmd varchar(5000)
SET @filename=YOUR BACKUP FILE WITH FULL PATH.bak'
SET @cmd='"YOUR WINRAR INSTALLATION PATH\rar.exe" a THE COMPRESSED FILE NAME WITH YOUR DESIGNATED PATH.rar '+@filename
EXEC XP_CMDSHELL @cmd
3. Use robocopy to copy the file (you can add this as another jobstep after the compressing file step)
xp_cmdshell 'robocopy SourceDirectory DestinationDirectory compressedfilename.RAR'and kick off the next step
4. On the desitination server:
Set up job to decompress the rar file
EXEC XP_CMDSHELL '"Your WINRAR Installation Path\winrar.exe" e COPIEDCompressedFileNameWithPath DestinationPATH'
5. You got your backup file on your destination.
Note: make sure the proper access permissions are in place for the above to work.
-
Max Wei (7/17/2009)
1. Install WINRAR (free) on both servers.Point of order: WinRAR is not free for production use. There is a 40 day evaluation license for no charge, but after that need to purchase licenses to stay in compliance.
7-zip[/url] is a LGPL with a section of unRAR restrictions, and would satisfy a "free and still legal" requirement even for commercial use.
Most larger corporations have a compression utility of choice that's licensed and approved, of course.
-
Thanks for pointing that out, Nadrek.
-
Thank you to everyone for your responses thus far. They are appreciated!
We believe we have a license agreement with WinZip - I will look into that further.
I will also look into the other tools mentioned and talk with our server guys.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply