Welcome back, in part one we took a look at four different software packages that have many, many features, but all had one thing in common, the ability to compress backups. If you haven’t read part one please review it here. The four solutions we chose for this article were SQL LiteSpeed by Imceda, SQL Safe from Idera, UltraBac from UltraBac Software, and SQL Backup 3.0 from Red-Gate Software.

The hardware and software configuration for the next set of test shows the other end of the spectrum. The server is an HP LH6000 with six 700mhz PIII processors with eight gigabytes of ram attached to an older EMC 3700 with 128 disks available. The OS is Windows 2000 Enterprise configured as a cluster with SQL Server 2000 Enterprise built 8.0.818 installed on it. The sample data is a typical set of data you would find in any companies CRM solution. The actual data size is 42.6 gigabytes in an 89.8 gigabyte database spread across eight files in a single file group. The tests were setup this way to make sure that the software you use to backup your smaller machines is still appropriate on your most critical servers.

Unfortunately, UltraBac failed to backup the cluster. I did speak to their technical support and followed all the instructions and recommendations they made. Even as far as loading a secondary machine separate from the cluster setting up a network alias to the cluster name in the client network utility, nothing worked. Similarly, SQL Backup 3.0 also wouldn’t backup the cluster. I got in contact with Red-Gate Software, they were very responsive and within a week had a new build that did backup the cluster successfully.

I ran all four products and a standard backup to compare them to. All backups were run at the lightest level of compression, if it was settable, everything else was left at default. All backups were written to a different set of drives separate from the data and log drives that were allocated to SQL Server.

I gathered performance monitor counters to get a reading on how much stress is being put on the server to perform the backups requested.

These numbers are well within what I expected. It just goes to show you testing on real world data always generates the best results. The numbers are broken up into two sets, the first four samples shows average CPU utilization and the stress put on the set of disk the backup files were places on. The second set is the drives that the database files were on. The load, as we expected, is greater on the CPU but, in some cases, much less on the disk subsystems. The only exception to this is SQL LiteSpeed, but it makes up for the added stress by being the quickest in the field.

These are stats reported back ether through the GUI or through query analyzer.

As you can see all solutions that did operate in the clustered environment did pretty well. Even the least expensive product produced a compressed file much smaller than the original backup with a significant savings in backup time.

Backing up a SQL Server in record times with great savings on disk space is one thing, what about restoring these databases in a timely manor? I’ve never been in a restore situation where time wasn’t a factor.

In part one of this article we used a smaller machine configuration. All of the backups taken were also restored. No additional parameters were passed to the restore statements all databases were restored over the top of the original database.

These times may look a little strange. Two of the apps I used the GUI to restore the database just like I did to back them up. All of the restores generally took less time than the standard SQL Server restore commands. SQL Safe edged in when it came to the report in milliseconds but the time in Query Analyzer shows a small increase over the standard restore. I may chalk this one up to the fact the command is run by calling the xp_cmdshell. Overall, in the smaller server configuration UltraBac had the times to beat in the backup process and restore, it did turn in the lightest amount of compression overall. SQL LiteSpeed was the next best and had a decent amount of compression.

In the cluster configuration these were the numbers returned for the restore.

In our clustered real world data set all programs beat the standard restore times by a significant margin with SQL Safe turning the best time overall.

Lessons Learned

To be honest with you, I had a mild bias when starting these test. I have been a long time user of SQL LiteSpeed and really expected it to just walk away with most of the test scores. I was initially skeptical of SQL Safe, being so new on the market and being a direct competitor against SQL LiteSpeed, who has had years to perfect the product, could do so well in these test. Another great surprise to me was how well a $295 dollar tool stood up against this field. I wasn’t surprised when it failed to run in a clustered setup, what did shock me is how quickly Red-Gate Software turned around a new build of SQL Backup 3.0 that did work, and work well. The only disappointment was UltraBac. It did perform well in the standalone, which was a plus, but configuration but failed to run in a cluster setup no matter what I tried. Also, the GUI left much to be desired. It feels like they are trying to be all things to all people, and not doing SQL Server backups justice. Personally, I would say it is a coin toss between SQL LiteSpeed and SQL Safe as the top two contenders. SQL LiteSpeed was faster on backup and SQL Safe was faster on restores, both have a great feature set without complete pricing information I would be hard pressed to pick the best between these two great products. Both Products have new releases in the works 4.0 of SQL LiteSpeed is due in the middle or late November, SQL Safe has 1.2 due out now. If you must have compression in your backup scheme and you are on a tight budget SQL Backup 3.0 is a great alternative. At ten times less the cost of even SQL Safe it is a fantastic value.

If you have any questions about this article, or methods used in the evaluation feel free to send email to admin@wesworld.net.