Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Four of a Kind - Backup Software Shootout Part 2

By Wesley Brown,

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

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.

Type

CPU

% Avg Disk Time Backup Drive

Avg Queue Length Backup Drive

Avg Read Bytes/Sec Backup Drive

Avg Write Bytes/Sec Backup Drive

% Avg Disk Time DB Drive

Avg Disk Queue Length DB Disk

Avg Read Bytes/sec DB Drive

Avg Disk Write Bytes/sec DB drive

SQL Standard

1.59

70.63

0.71

0.00

14887214.30

283.22

2.83

14876105.52

1521.16

SQL Backup 3.0

14.56

15.35

0.15

151.97

3825357.96

148.96

1.49

23005421.18

1414.37

SQL LiteSpeed

12.57

33.89

0.34

0.57

5869727.27

391.09

3.91

30909523.40

1641.73

SQL Safe

12.49

28.07

0.28

432.87

4929578.58

112.84

1.13

25693516.68

1393.89

UltraBac

0.00

0.00

0.00

0.00

0.00

0.00

0.00

0.00

0.00

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.

Type

File Size

Time reported by command output

Time in App

MB/Sec

Standard

34,516,316

39.59

39.37

14.88

SQL Backup 3.0

5,736,467

25.45

25.45

23.14

SQL LiteSpeed

6,586,690

18.06

18.57

32.62

SQL Safe

6,586,829

22.66

22.46

26.00

UltraBac

0

0

0

0

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.

Type

Time Reported in QA

Time Reported In App

MB/Sec

Standard Restore

8:29

7.90

8.388

SQL Backup 3.0

0

7.24

9.152

SQL LiteSpeed

8:00

6.58

10.072

SQL Safe

9:06

7.34

9.035

UltraBac

0

5.57

8.037

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.

Type

Time Reported in QA

Time Reported In App

MB/Sec

Standard Restore

48.27

47.73

12.34

SQL Backup 3.0

0

36.56

16.11

SQL LiteSpeed

36.15

35.63

18.65

SQL Safe

32.32

31.59

18.64

UltraBac

0.00

0.00

0

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.

Total article views: 7237 | Views in the last 30 days: 3
 
Related Articles
ARTICLE

Restore from LiteSpeed

Learn how to restore a LiteSpeed backup on a server without the software in 2 Simple Steps.

FORUM

Litespeed DoubleClick Restore

Issue - Litespeed DoubleClick Restore

FORUM

Litespeed Vs Native Backup?

Litespeed Vs Native Backup?

ARTICLE

Backup and Restore Back to Basics with SQL LiteSpeed

This article analyzes the various options available for your backup and recovery process with SQL Se...

FORUM

DB Restore using LiteSpeed

DB Restore using LiteSpeed shows no progress

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones