Fiddling around and the simple storage solution

I was invited on to the Amazon Computing Cloud yesterday - a seminal moment as I have been desperate to have a go on it for weeks now, but disaster! I was passed over because I had foolishly forgotten to open an S3 (simple storage service) account which is a prerequisite, despite my grovelling and the immediate opening of an S3 account I appear to have missed my window of opportunity.

That said it has given me a little time to investigate the simple storage service and in itself this has opened a few doors in my mind. Storage in the S3 bucket is very cheap indeed at 15 cents per Gig per month, this means for example that I could store my largest SQL database, at 2Gig and rising, on the ultimate storage system for 14p per copy per month with transfer costs of 10p. Doing a little simple maths therefore means that backup scenarios would cost:

• 24p per month per copy

• £1.78 per copy per month

• £50 per month for a rolling 30 day backup

• £650 pa. for a daily backup rolling on for 12 months (almost 800Gb of data)

The real magic here is not that I can get someone to host almost a terabyte of data for £650 pa but the infrastructure behind it, storage spanning multiple data centres in multiple countries, is fantastic. There are only 2 slight problems, there is a file size limit of 5Gb at present which hopefully will have increased by the time my bloaty database has got anywhere near that, also my backup software Red Gate does not at present support S3.

So I'll have to write a little program one of these days to bounce my off site backup from our Manchester server over to the S3 cloud on a nightly basis then I really, really will have an offsite backup I can be satisfied with. Alternatively our friendly Red Gate developers might like to take the hint, get an S3 account and crack their programming type knuckles. Utility computing has to be the future of off site backup so get in there, I wont charge for this revolutionary piece of advice but I have broken my promotional Red Gate pen so if someone wants to send me another one we'll call it quits :o)

Whilst I am on the subject of S3 there is a fantastic little add on for FireFox which I found very useful for getting things moving, the S3 organizer, its a good job the programming is better than the spelling of organiser :o)

And speaking of really useful little pieces of software anyone who does any programming of web apps should get themselves a copy of fiddler. Its a really rather sweet little program written by Microsoft (tun tun tun!) and distributed free! Maybe Bill finally has enough.

Anyhow before I get on my soap box, fiddler is a very simply http proxy server which gives you a real time readout of what traffic is moving through your Internet connection. As we are using Flash couple with AmfPHP we constantly struggle to debug in an efficient way but not anymore! I wont spoil compadre Robs review right now but just get it and watch out for the bug eyed review.

Finding extra space on a VMWare Virtual Machine

Now our Redgate SQL Server backup system is running nicely it has exposed a slight deficiency in my server setup over in Manchester, the problem now is that when I created virtual machines for all our little applications running on the VMware system I only gave then 4GB disks :o)

For the Nagios system, the Intranet applications, the source code repository and the knowledgebase these disks are perfectly adequate but for database backup obviously a bit more space is required. Having utilised a spare folder on the knowledgebase virtual server for the SQL Backup it would have been very inconvenient to erase the machine and create a new one and also given the fact that the host server is not overburdened with RAM I didn't feel it would be wise to put more than 4 machines on the system.

So we come to a less well documented feature of a VMWare virtual machine, when you set the hard disk size on creating the machine you cannot increase it in the future. So you have 2 choices, the Lego approach - smash it up and start again, or the clever approach, add on a new virtual disk. I chose to be clever and in a nice twist of fate got away with it :o)

So I think a little how-to is in order.

Begin by adding a new disk in VMWare, it was recommended that I choose a SCSI disk so I complied, bear in mind this has to be done with the virtual machine powered down. When you have added the disk just power up again and you are there, all you have to do now is to get your Linux install to make use of it.

As I have blogged before our flavour of choice when it comes to Linux is CentOS but RHEL4 or Fedora would probably work in exactly the same way because its pretty basic stuff really.

First use Fdisk to create a new partition, as this is our second disk its SDB (SCSI Disk B)
so:
fdisk sbd
and then just follow the instructions to create a new partition.

Next you need to format the new disk so:
mkfs.ext2 /dev/sdb

At this point you need to check the space on the disk so:
df
and you should see your new disk listed

Finally you need to mount the disk so:
mount -t ext2 /dev/sdb /home/samba/sql

As I was using samba to share the /home/samba folder all I needed to do was update the permissions on the folder and restart the service - job done.

Now I have a bit more space on the share the backup has worked a treat, it is one of the real strengths of the Red Gate system that you can see immediately how your backup routine is performing using the timeline GUI. As you can see from the picture here the first couple of databases backed up nicely but given that they are larger databases they are a bit snug so I might just ease them apart a little so we don't get a clash as they grow.

Remote SQL backup onto SAMBA Shares

Backup is a subject which comes up a lot on the rack as regular readers will already have noticed and I have already detailed several of the strategies we employ to make sure we have a myriad of copies of our essential data spread across the network, preferably as far apart as possible! Our latest investigation concerned how to get a usable copy of our most precious and bloatie database from our SQL Server 2000 installation across the VPN (and therefore Cheshire) on a regular basis.

Time for a topical and amusing dip into Google images for the word of the day - bloated, as in a very large database. This little marmot, who obviously has a small pie problem represents our database for this afternoon.

The data to be moved is 1.3 GB over a 2MB line from a Windows 2003 Server to a Linux partition on our CentOS virtual machine. Given all the other scheduled adminnie jobs we have going on overnight I cannot afford for the whole process to take more than about 30 minutes, I sometimes think the network is busier out of office hours!

The solution is of course to compress the backup before sending it and it turns out that after a little investigation there are a couple of products on the market which do this for you. After only a brief search I found SQL Backup by Red-Gate Software and Lite Speed for SQL Server by Quest which both offer on the fly compression and encryption to boot. One might have expected Microsoft to include a compression option in their rather expensive server system but one would be wrong as usual (thanks Bill). Bit less time writing the EULA and more on the software next time eh?

Moving swiftly on, the products mentioned above are relatively simple in their approach in that they add some system stored procedures to your SQL Server install which can be scheduled to run, adding compression and or encryption to a standard full or differential backup. The cost is quite manageable as well at $45 to $399, which is a lot less than the time would cost to build our own script! But incidentally if anyone is interested here is a start. Installing the programs on our test bench was easy and initially everything was very straight forward until we tried to send it to our Linux machine....

In order to share a folder on Linux one requires the cooperation of a service called SAMBA which is really quite powerful and therefore complicated, sharing a folder to any Tom Dick or Harry is very well documented and quite easy. Authenticating against our active directory and sharing with our windows machine requires the services of a good soothsayer however and is somewhat sparsely documented!

Having finally got a shared folder onto the windows network I thought the job was done but unfortunately I hadn't counted on a couple of less well documented features of SQL server.

1. SQL Server does not like backing up to network shares that are not in the same work group
2. SQL Server will not offer to re authenticate, the user account SQL Server logs in on must have explicit and full access to the network share.

I would love to give a blow by blow account of how I got the network share going but I have been chipping away at this problem sporadically and unfortunately I have sort of lost track of how I got where we are.

Having taken a while to sort this I have finally got some comparative data and I am very impressed indeed! Given that a 2MB line is really quite modest, SQL Backup managed to compress, encrypt and squeeze a 1.4 GB database down to 250 MB and ferry it across Cheshire in 20 minutes and 10 seconds! It took me a while longer to get Lite Speed up and running but at only $45 it ripped through the compression and transfer in a mere 10 minutes and shaved 30 MB of the storage requirements at 220 MB! Its my new best friend and I would recommend it to anyone looking to get their database backups as far away from them as possible. The only thing left to sort out is that I cannot afford to change the logon account for the main server as I did on the test machine so hopefully I can get SAMBA to cooperate with the existing setup this time :o)