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)