Technical overview - SQL Storage Compress

Introduction

SQL Storage Compress comprises:

• The HyperBac Compression Engine Components

  – required to create and use SQL Storage Compress databases.

• The SQL Storage Compress user interface (optional)

  – includes a database browser to help you analyze the space savings you could achieve by compressing your databases and a wizard to help you create compressed databases.

For information on how to install, see Installing and activating SQL Storage Compress.

How does SQL Storage Compress work?

SQL Storage Compress compresses database data files (.mdf and .ndf) and transaction log files(.ldf) using the HyperBac Control Service, without SQL Server being aware of it. It does this by performing the compression and decompression at a low level of the operating system, from inside the Windows I/O Manager.

Whenever SQL Server needs to read from or write to disk, the request is passed to the Windows I/O Manager. When the HyperBac Control Service is installed, it intercepts disk reads and writes to files that use the SQL Storage Compress file extensions (.mdfx, .ndfx and .ldfx), and the data is compressed or decompressed as required.

Because SQL Server is unaware of the compression and decompression performed by the HyperBac Control Service, it continues to handle your databases as normal. All database operations are supported, whether through T-SQL statements or a SQL application, including inserting and updating data, creating tables, taking backups and running queries. Any data that is later inserted or updated will also be compressed.

Note: compressing the transaction log (.ldf) file is not recommended for transaction-intensive databases, as this can place a heavier load on the CPU.

Encrypting database files

SQL Storage Compress can also be used to encrypt database files. When the extensions .mdfe, .ndfe or .ldfe are used, the HyperBac Control Service encrypts or decrypts the files by intercepting the reads and writes, as described above. Encrypted files are also compressed. The SQL Server instance remains unaware that the database files are encrypted and compressed.

Note: if you choose to encrypt a transaction log file (using the .ldfe extension) the file will also be compressed. This can result in a heavier load on the CPU for transaction-intensive databases.

Creating SQL Storage Compress compressed databases

To create a compressed version of an existing database, take a backup of the database and restore it to files using the SQL Storage Compress file extensions (.mdfx, .ndfx and .ldfx). During the restore process, the HyperBac Control Service intercepts the data that SQL Server writes to disk and compresses it.

You can restore to a compressed database from a backup in one of two ways:

• Writing a T-SQL script

  Use a RESTORE DATABASE statement with MOVE 'logical_file_name' TO 'operating_system_file_name' to restore the database files using the SQL Storage Compress file extensions. For example:

  RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Backups\AdventureWorks_Full.bak' WITH MOVE 'AdventureWorks_Data' TO 'C:\ProgramFiles\MicrosoftSQLServer\Data\AdventureWorksDW.mdfx'

  All types of backup can be restored to any recovery model. All backup file formats are supported, including backups created by 3rd party tools, provided the software is installed.

  For more information see Using T-SQL scripts.

• Using the SQL Storage Compress wizard

  If you have installed the SQL Storage Compress user interface, you can use the wizard to restore to a compressed (or compressed and encrypted) database from a full backup. The following backup file formats are supported: native SQL Server (.bak), SQL HyperBac (.hbc, .hbe, .hbc2, .zip, .rar), SQL Backup Pro (.sqb) and (unencrypted) Litespeed (.sls, .bak).

  Note: when compressing a database using the wizard the log file is not compressed.

  You can also use the wizard to generate a T-SQL script which you can subsequently modify and execute manually.

  For more information see Using the SQL Storage Compress wizard.

Once the restore process is complete, the compressed database is available to use immediately and the backup file is no longer required.

Creating a new compressed database

To create a new compressed database, use the SQL Storage Compress file extensions when you specify the locations of the data and log files. For example:

CREATE DATABASE Sales_SSC

ON (NAME = Sales_SSC_data, FILENAME = 'C:\ProgramFiles\MicrosoftSQLServer\Data\Sales_SSC.mdfx')

Viewing database file sizes

Because the SQL Server instance is unaware of the data compression provided by SQL Storage Compress, file sizes recorded in the SQL Server data dictionary always represent the size of the uncompressed files. This may give the impression that the data files have not been compressed.

To see the actual, compressed size of each database file, use the SQL Storage Compress user interface or view the files directly in Windows Explorer.