Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 276

6. Let us query the statistics of both the backup execution processes using the

backupset system table:

SELECT database_name, backup_size, compressed_backup_size
FROM msdb..backupset where database_name='AdventureWorks2008R2'
ORDER BY backup_finish_date DESC

The results are as follows:

database_name backup_size compressed_backup_size

AdventureWorks2008R2 10568704 10568704

AdventureWorks2008R2 10569728 1455709

AdventureWorks2008R2 10569728 10569728

You should now have implemented backup compression for a VLDB environment.

How it works...

The backup compression feature allows you to create database backups that run faster and
occupy less disk space. In addition to the space savings, the compression of the backup
increases the backup speed due to less I/O requirement on disks. However, the I/O cost savings
will always come at the expense of increased CPU usage caused by the compression process.

As a best practice, it is recommended that the VLDB
backup jobs or job steps are scheduled during the
usage hours on the SQL Server instance.

The backup compression feature can be enabled server wide using the SP_CONFIGURE
statement or the WITH COMPRESSION option within a BACKUP statement. However, if there
is a need to not use the backup compression for a particular database, then this can be
achieved by using the WITH NO_COMPRESSION statement as demonstrated in the previous
sections. The status of the backup set can be obtained by querying the backupfile
and backupset system tables in the msdb database. If the database backup was not
compressed, this value would be the same size as the backup_size column.

Designing change tracking methodology

Change Data Capture (CDC) and Change Tracking (CT) can be classified as the same names
but both have different purposes, which are introduced in the SQL Server 2008 version.

CDC is an asynchronous process mechanism that captures all changes of a data row from the
transaction flow and stores them in change tables. CDC is useful in avoiding the expensive
query methods such as triggers or multiple-join to capture the changes made to the data.