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