Four of a Kind, Backup Solutions That Provide Compression:Part 1

All DBA’s are faced with the daunting task of backing up databases under their charge, and sometimes a more daunting task of restoring them. In this article we will look at four different backup solutions that can make that job easier. The thing that ties these four products together is compression. All of them offer different feature sets to set them apart and we will touch on those, but the focus here is real time compression and how it can help you. The theory behind compressing the data stream is simple the results can be dramatic. Almost every return on investment figure uses $200 to $300 dollars per gigabyte saved. This usually includes things that are easy to calculate like physical disk space, tape, and additional hardware required to store your backup files. It also includes other aspects of cost like the hours it takes to administer the disk, and tape systems, also time to backup and restore. 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. Lets take a quick rundown of the other features each provides besides compression.

SQL LiteSpeed

Imceda’s SQL LiteSpeed has several different options beyond compression. They also provide encryption as an add-on you can buy. New in 3.2 release feature object level recovery even from standard Microsoft backups and multiple encryption algorithms to choose from. Backups are also multi-threaded into a single file to increase performance. The standard configuration comes with an MMC snap-in if you require a GUI interface that is easy to use and also includes wizards to convert your SQL Server generated maintenance plans to use SQL LiteSpeed.

They also have a fully documented extended stored procedure library that is used to replace the backup and restore syntax in your SQL statements. Pricing is per processor and is not currently listed on their web site.

SQL Safe

Idera’s SQL Safe is a new comer to the SQL Server backup world and comes on strong. It was designed to compete with SQL LiteSpeed and offers multi-threaded, encrypted backups just like SQL LiteSpeed. It also has a nice user interface.

One of the features that separate the two products is the lack of extended stored procedures, the addition of a database that keeps an audit trail for backups and restores and a lightweight installer. The lack of extended stored procedures means you will have to use xp_cmdshell if you wish to still use T-SQL scripts to backup your databases. The database it adds initially bothered me. Personally, I like minimal intrusion or things I have to maintain along with my backups. In the process of reviewing the product I found I like the database and the things it stores for you, it makes it easy to prove to management that you really are saving the money you claimed and also provides a long term audit trail for those of us that have to worry about compliance issues. Pricing is per server and is currently $995 dollars per SQL Server instance.

UltraBac

UltraBac Software’s offering UltraBac does much, much more than SQL Server backups. I won’t list them all here. Agents installed on the target server handle all backups. So you will need the base and the SQL Server agent to backup your box. It comes with a GUI that seems a bit cluttered.

It also comes with a command line interface but I didn’t use it to do the SQL Server backups. I did have some issues backing up a clustered environment, which we will cover later. It was very stable and fast on a standalone SQL Server. One of the qualifications for this round up was the ability to backup to disk. UltraBac does this by putting a virtual device that it can write to. It doesn’t look like your normal file generated by any of the other products, it rolls them in files that are two gigabytes in size until the backup is done. Base pricing from the web site $495 for the base and $695 per SQL Server agent.

SQL Backup 3.0

Red-Gate Software’s SQL Backup 3.0 (formerly SQL Backup 3.0) provides excellent functionality for the price point it is at. It offers GUI, command-line, and extended stored procedure interfaces in the pro version.

Other than compression, it offers mirrored backup, the backup stream gets written to two files, either one will restore the database. SQL Backup 3.0 Pro is $295 per server.

We will look at both ends of the server spectrum, large and small. This first set of test is on a smaller hardware solution with a standalone install of SQL Server patched up to version 8.0.818.

Small setup consisted of a single LP1000r NetServer from HP. Dual 1ghz PIII with three 73 gigabyte hard drives striped RAID 0 for speed. I used a copy of the Northwind database populating Categories and Employees with around 900,000 records each to take the database size to 3.7 gigabytes of space used.

I ran all four products and a standard backup to compare them to. All backups were ran at the lightest level of compression if it was settable everything else was left at default. All backups were performed to the same array.

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

As you can see SQL LiteSpeed and SQL Safe both used about the same amount of CPU. SQL Safe put more pressure on the disk systems than SQL LiteSpeed. The Disk stats are puzzling I expected them to all use the same amount of disk bytes or be close to each other and a standard backup to be more which it was. That seemed to be the only thing that stayed true.

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

These numbers also seemed out of line with my personal experience and with numbers in the second set of test. I think this may be a side effect of almost zero blank spaces in the random sample data, since it was random letters and numbers and not random whole words.

All products in this first phase of the testing worked as promised and without any issues at all. In part two we will deal with ten times the data on a much larger machine in a clustered setup.