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

SQL 2005 vs. SQL 2008 Part 1 - (Backup File Sizes and Times)

By Kevin van der Merwe, (first published: 2008/05/05)

Welcome to the first in a series of articles highlighting some of the key improvements SQL 2008 has over SQL 2005.

Backup File Sizes

Databases today are growing bigger and bigger by the day and this seems to only be the start of what we can see in the near future with more content being accumulated and stored than ever before.

In SQL 2005 there was no capability of the engine to compress database backup files to disk at the time of the backup being executed.

In SQL 2008 there are 3 options while backing up databases, these are:

- No Compression (same as SQL 2005)
- Server Setting (if server backup compression setting is on/off - use this setting)
- Compression (Compress backup file during backup)

Scenario

We have a 3.5GB Database Backup File from a SQL 2005 Server Backup.

This 3.5GB Database was then restored to the SQL 2008 Server
(Note: No additional transaction activities on this database after restore)

After the Restore:

 

SQL 2008 Backup Options:

Backup without Compression:

This will be the same as the SQL 2005. The General Page does not have any visible changes as can be seen below:

 

 The second Page - under Options has a new section titled "Compression" - here we get our first look at the options available:- Use the default server setting
- Compress Backup
- Do not compress backup

For this first option we are going to be using "Do not compress backup" CPU Activity during Backup without Compression:

CPU activity is for the entire backup process - averaging approximately 18% CPU usage. SQL 2005 BAK File Database SQL 2008 Backup File - no compress:

Note these are virtually identical in size - as the backup method used is the same for:
SQL 2008 and SQL 2005
Backup WITH Compression:The Options Page now looks like the following:

For this first option we are going to be using "Compress backup" The SQL 2008 Backup File Size - using the above setting:SQL 2008 - No Compression SQL 2008 with Compression

 CPU Activity during Backup WITH Compression:

Note the CPU usage during backup with Compression is on average 35% - approximately double

File Size Summary

The SQL 2005 .BAK file that we created the Database with was 3.5GB
The SQL 2008 Backup without compression was 3.5GB
The SQL 2008 Backup with compression was 986MB. For this database the reduced space is 2.5GB; the compressed file is only 28% of the original size!

Note: Not all databases will have this compression factor or CPU usage due to systems and data types etc.

Backup Time Summary

The SQL 2008 Backup without compression took approx 6 minutes and the SQL 2008 Backup with compression took approx 3 minutes. You might be asking - how is that possible?

Well the CPU is used to compress the data before it is written to disk, so LESS data is written to disk. The less data that is required to be written to disk the faster this can be done by SQL Server and the operating system. 

Summary

With databases growing daily, the knock-on effect of this additional data has various cost and time factors. If you are managing a 100GB database, the speed of the backup window, which can now be reduced, is excellent news for DBAs.The size of the actual backup file that needs to be stored on disk and then archived to tape or preferred method is using up less space, which reduces costs ultimately.The quicker backups as well as smaller backup files are more than enough to warrant an investigation into SQL 2008, especially for VLDBs.

Until next time.


Kevin

 

 

 

Total article views: 22124 | Views in the last 30 days: 16
 
Related Articles
FORUM

Best backup compression tools

Best backup compression tools

ARTICLE

SQL Server 2008 Compression

Testing and analysis of new backup compression and table/index compression features of SQL Server 20...

BLOG

SQL Server Data Compression

SQL Server Data Compression Overview I have been reviewing the options for data compression in SQ...

BLOG

Testing Native Backup Compression in Combination with SQL Server 2008 R2 Data Compression

SQL Server 2008 introduced native backup compression and data compression as features that were only...

BLOG

SQL Server || Compress & UnComprees Backups in one Media\File

Today, We try to check of SQL server when trying to take Compress & UnCcomprees Backups in one Media...

 
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