MSDN Blog Postings

If you haven’t looked into SQL Server 2008, and you have a large database, we suggest that you get an evaluation copy. You’ll want to explore one of the best features Microsoft has added to SQL Server in a long time: data compression.

Data compression can help you keep the hardware costs for RAID and hard drives under control when you run Teamcenter.

SQL Server 2008 actually has two types of compression in it, data compression and backup compression. Although backup compression can be a good thing, most of us who maintain very large databases mostly use third-party backup tools or take our backups using a SAN snapshot, which provides backup compression.

What is Data Compression?

Data compression provides a solution to the challenges of managing very large databases. Using this feature, a DBA can selectively compress any table, table partition, or index, resulting in a smaller on-disk footprint, smaller memory working-set size, and reduced I/O.

SQL Server 2008 supports two types of compressions:

· Row compression, which compresses the individual columns of a table

· Page compression, which compresses data pages using row, prefix, and dictionary compression

Although the amount of compression that is achieved is highly dependent on the data types and data contained in the database, row compression generally results in lower overhead on the application throughput with marginal space savings. Page compression has a higher impact on application throughput and processor utilization but results in much larger space savings.

Consider a hybrid approach—compress only the largest few tables. This approach saves significant disk space and has a minimal negative impact on performance.

You can implement data compression via Transact-SQL or the Data Compression Wizard. To determine how compressing an object will affect its size, you can use the sp_estimate_data_compression_savings system stored procedure or the Data Compression Wizard to calculate the estimated space savings. For more information about using compression, see Creating Compressed Tables and Indexes.

Start your investigation of data compression by reading this excellent technical article from Microsoft: Data Compression: Strategy, Capacity Planning and Best Practices.

Backup Compression

Backing up a large database can require a significant amount of time and a large amount of disk space for the backup files. With SQL Server 2008 backup compression, the backup file is compressed as it is written out, thereby requiring less storage, less disk I/O, and less time.

You can achieve the compression by specifying the WITH COMPRESSION clause in the BACKUP command or by selecting it on the Options page in the Back Up Database dialog box. You can also use a global setting to compress all backups that are taken on a server instance by default. (You can access this setting by using the Database Settings page in the Server Properties dialog box or by running the sp_configure stored procedure with the backup compression default set to 1.) The restore command automatically detects that a backup is compressed and decompresses it during the restore operation. For more information, see the article Tuning the Performance of Backup Compression in SQL Server 2008 on SQLCAT.com.