Our primary SQL Server application was growing much quicker than expected and we were starting to run out of space for backups. The production database (100 gb) was on a Windows Cluster with a fast SAN. The options to fix the problem were to buy more SAN space (disks) or buy a product that compressed the backups. I considered Red Gate's SQL Backup, Idera's SQL Safe, and Quest's LiteSpeed, but I decided to only evaluate LiteSpeed Standard because I felt the others were too new and this database has to be recoverable. LiteSpeed has a long history of favorable recommendations and its partnership with Microsoft just seemed to justify it.

LiteSpeed versus SAN

Now was the time to evaluate LiteSpeed as opposed to buying more SAN space. LiteSpeed had a few annoying bugs but otherwise worked well with the database maintenance plans we love to use. The run times for backups and restores between the two approaches were close. The reduced backup files sizes from LiteSpeed were what I expected, so the remaining criterion was cost. LiteSpeed was more affordable than SAN disks, so I submitted a purchase request for LiteSpeed Standard Edition for the production cluster and development servers. I did not request LiteSpeed Professional Edition because of its cost.

Given a new Direction

After waiting two weeks for my purchase request to be approved, it was kicked back by upper management. They wanted me to look at backing up directly to TSM (Tivoli Storage Manager) because that is how our Oracle databases on UNIX where being backed up. TSM is an IBM product that performs file backups and stores them on tape. An Oracle DBA informed me they where using a tool from IBM called TSM Data Protector for Oracle (catchy isn't it). Going to IBM's website, I found they had a version of the product for SQL Server. It was called TSM Data Protector for SQL Server or TDPSQL for short. IBM's website was not offering any evaluation download or prices on-line so I approached our TSM Admin for both of these, and I received a price quote that was more than LiteSpeed Professional. The TSM Admin installed the product on the two servers I used to evaluate LiteSpeed and it was time for some testing.

LiteSpeed versus TDPSQL

I used two servers for my testing: one server was configured with one large raided drive that contained both the database files and backups. The other server was identical to our production clustered server except it wasn't clustered and had only 2 CPUs while production had 4 CPUs. The backup files and database files where stored on separate fast SAN drives (LUNS). I expected the run times on the server with the one raided drive would run quicker than the TSM backups because of the reduction of I/O contention. This was proven true in all my tests.

Nice TDPSQL GUI

The TDPSQL GUI interface was friendlier than most IBM products. I was able to create a simple command script that I included in a SQL Agent job, however, it cannot automatically create the job as LiteSpeed did. On the server with one drive the run times where quicker than the LiteSpeed to Disk option. Next I tested TDPSQL on the production-like server. My first tests showed long run times for the backups. That's what I get for using default settings. Increasing the "stripe" setting, I was able to get much more reasonable run times. I then increased the "buffersize" setting from 1024 to 4096 but did not see any noticeable change in times. TDPSQL was looking promising.

Testing With Production Database

It was time to test these products using an old copy of our production database (50 GB). Up until now the largest database I was testing was a 28 GB test database that was populated with a lot of duplicate data, which compresses very well with LiteSpeed. To get some base timing, I used the native SQL Server tools to create a disk backup of the old production database (50 GB) which took 15:22 minutes and the restore took 10:46 minutes. Funny, these run times are only 1-2 minutes longer than what I got with the 28 GB database. I am using the same server but different LUNS because I can't fit both databases on the same LUN.

Time for the Backup to TSM tests

The TDPSQL backup and restore were 41:48 minutes and 1:35:44 hour respectively. For the LiteSpeed tests I used compression 1 without VERIFYONLY. The backup and restore times were 36:43 and 38:11 respectively. As you can see the LiteSpeed product is quicker especially on the restore. But when you compare the run times to using SQL Server backup to disk, they both perform poorly. The run times for TSM products against the 28 GB Test database were about 50% quicker, this is what I expected considering the smaller database size.

SQL Safe

After more tests of TDPSQL and LiteSpeed, I found I like the ease of use of TDPSQL better than LiteSpeed. Still not happy with TDPSQL run times and LiteSpeed's approach to TSM database backups and restores, I decided to look at Idera's SQL Safe. It was a quick review because it did not yet support the new TSM 5.3 release which we use. It still only supported TSM 5.2. Too bad, maybe next time.

Here is a summary of my results:

TDPSQL

• Pros
• Backups can be scheduled from TSM
• Easy to create scripts for backup commands
• Easy to create and execute restores
• GUI is much easier to use than LiteSpeed - TSM

• Cons
• Must remote desktop to server to use GUI
• Restores take up to 85% longer than LiteSpeed and 415% longer than restores using a SQL Server disk backup
• Does not verify backups

• Little Things that Bother Me
• The tool is case sensitive to database names
• Restore failed from lack of space but the error message made it seem like the backup had an I/O error
• Restoring Master database gets an error but still worked

LiteSpeed

• Pros
• Faster backup/restores when compared to TDPSQL
• Has a backup verifyonly option just like SQL Server. It does double the run time because the file is processed twice.
• Professional edition provides table only restores from backups on disk

• Cons
• Backups scripts require much more customization for scheduled backup
• Restores can be confusing in setting them up
• The latest release did not address these problems and doubled the run time for backups

• Little Things that Bother Me
• The wizards for the backup and restore act differently when identifying the filespace
• GUI can be installed on remote desktops but has problems accessing the TSM DSM. OPT file
• Need license to restore on another server

Conclusions

As a former mainframe DB2 DBA I like the idea of backing up directly to tape. Management likes the idea of not having another software vendor to deal with. Plus both Oracle and SQL Server DBA's would be using similar tools to perform backups and recoveries. Consistency is a virtue.

LiteSpeed is working on a product for Oracle, which is nice to hear, but I don't recommend products that are being developed based on a vendor's other products. LiteSpeed is quicker but has some quirks in the way you identify backups for a database. Both tools are still slower than SQL Server backup to disk approach, and I hate waiting for long restores.

What to do? To paraphrase Dr. Seuss's Cat in the Hat "What would you do?"

Here are some Screen Prints from the Products

TDPSQL Backup Database

(click for larger image)

TDPSQL Restore Database

(click for larger image)

LiteSpeed Backup Wizard

(click for larger image)

LiteSpeed Restore Wizard

(click for larger image)

LiteSpeed Restore Wizard Image 2

(click for larger image)