Automating Secure FTP Backups to a Windows 2008 Server

I’ve been looking into solutions for shipping database backups and log-files to a remote server as part of our backup strategy. Requirements are pretty simple:

• It’s got to be completely automated
• A daily run has to take less than 6 hours so it’ll finish overnight whilst our connection is quiet
• Transfers need to be encrypted to stop people sniffing our packets
• I don’t want to spend a lot of money
• If I can get away without lots of complicated (read: fragile) command-line switches, then that would be a bonus

Now, there’s four common protocols for doing secure file transfers - SFTP, FTPS, FTP+SSH, and SCP. The specifics aren’t important – what matters is that they’re incompatible, so your client and server have to be using the same protocol, otherwise nothing will work. There’s also various dedicated tools such as rsync, robocopy, xcopy, SyncBack, DeltaCopy… the list goes on. Setting up rsync on Windows is a bit fiddly – not to mention the dozens of subtle and nefarious command-line switches available to rsync.exe – and whilst I love a bit of cygwin hacking as much as anyone else, I’d really rather use something a little more intuitive here. I did try DeltaCopy, a GUI wrapper around rsync, but I quickly ran into security problems with SSH keys and username/password combinations. Since I wanted to avoid open folder shares on the remote system, tools like xcopy and robocopy were out.

In the past, I’ve had great results with Vandyke Software’s VShell, a commercial Windows SSH server, but whilst putting this lot together, I discovered that in Windows Server 2008, you can set up an FTP server to encrypt connections via SSL (it’ll even use the same certificate as your website). That’s good, by the way – I think things are always cheaper & easier when Windows does them out of the box.

So – assuming you’ve already got a Windows 2008 Server running IIS, and you’ve already got a certificate installed, then log onto the server, fire up IIS, create a new FTP site, switch on SSL, bingo.

Just to sanity-check that everything’s working, fire up WinSCP, create a new session using FTP with SSL Explicit Encryption, and you should be able to connect to your new secure server.

OK – so far, so good. We can connect, we can see a directory listing. Now what I want to do is to automate the client side of things, and for this bit, I’m going to use a superb bit of software called Super Flexible File Synchronizer. This is something I’ve only discovered recently, but so far, I’m delighted with it. It’s got native support for FTP. SFTP, WebDAV and HTTP, as well as Google Docs, Amazon S3 and Azure (!), plus a built-in scheduler, mail notification facility… as I said, it’s looking very promising.

Right, time for some actual computer science – complete with experimental data and graphs and everything. I want to know how fast this thing is going to go, and I want to know how much the encryption’s going to slow things down, so I’ve put together 72Mb of assorted database backups, TIFF files and documents, and transferred them up to the remote server using various settings. Results here are the average of five runs for each combination.

There’s a slightly odd setting in Super Flexible File Synchroniser – when you’re setting up an Internet target, if you pick FTP, it’ll give you a choice of libraries – cryptically called 1, 2 and 3.

I’m guessing that under the hood, there’s three different off-the-shelf FTP libraries in there, and the option lets you pick another one if the default doesn’t play nicely with your particular server. (Yeah, like anyone’s ever going to need that…)

So, numbers. I ran five different settings – network share (i.e. copying straight to \\myserver\backups using Windows file sharing), “open” FTP (no SSL), and then each of the three FTP libraries with the SSL option switched on.

A couple of things to note:

1. FTP Library 2 just didn’t work – it wouldn’t even connect to the remote server to retrieve a directory listing.
2. FTP Library 1 clearly had some issues – a typical run took well over eight minutes, and the subsequent logs were littered with timeouts and stuff like this:

COPY L->R d:\FileDemo\F00090820-0103.tif  (889.7kB)
FTP Exception (5): EclSocketError Timeout error occured @ 009283D0
COPY L->R d:\FileDemo\F00090820-0104.tif  (851.6kB)
FTP Exception (5): EclSocketError Timeout error occured @ 009283D0

Fortunately – FTP Library #3 worked perfectly, and gave transfer speeds that were actually faster than the raw FTP connection. I’m thinking that’s probably down to variations in test conditions (time of day, traffic. and so on) but even allowing for a little experimental error, it’s definitely fast enough to work with.

We’re looking at transferring about 20Gb worth of database backups and photographs a day. Based on the stats above, we can do 75Mb in 45 seconds, which equates rather neatly to 100Mb / min, which means our 20Gb backups are going to take just over three hours. $60 for a copy of SuperSynchroFlexofile O’Maticalyzer (and I’ll throw in a case of beer if you can come up with a snappier name for the next edition), SSL all the way, and a nice GUI to set it all up.

Isn’t it nice when everything just… works?