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.

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.

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.

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 installe

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).

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

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.