Technical overview - SQL Storage Compress

Introduction

SQL Storage Compress comprises two main parts: a Windows service (the HyperBac Control Service), and a Windows application.

The HyperBac Control Service must be installed before you can create and use SQL Storage Compress databases. The SQL Storage Compress application includes a wizard for creating compressed databases, and a database browser for analyzing the space savings you could achieve.

When you download the SQL Storage Compress software, two installers are provided: one for the HyperBac Control Service (plus additional tools), and one for the SQL Storage Compress application.

Read more about installing, upgrading, and activating SQL Storage Compress

How SQL Storage Compress works with compressed databases

The HyperBac Control Service silently intercepts read and write requests from the SQL Server instance as they pass through the Windows file system.

For each request, the service checks the associated file path and file extension to determine whether the request relates to a SQL Storage Compress database. The service then compresses or decompresses data as required.

The illustration shows a compressed database that uses the standard SQL Storage Compress data file extensions: *.mdfx and *.ndfx:

Because the HyperBac Control Service runs inside the Windows kernel, it is invisible to the SQL Server instance. The instance remains unaware that the data files are compressed, and operates as normal, reading and writing to the data files and log file.

This means that you can use the compressed database exactly as you would use a standard SQL Server database, while taking advantage of the compression processing of SQL Storage Compress to minimize the disk space required for the database.

By default, SQL Storage Compress compresses data files, but not transaction log files. In the illustration above:

• data reads and writes for the log file ("t-log.ldf") are processed by the operating system in the usual way
• data reads and writes for the data files ("data1.mdfx" and "data2.ndfx") are passed through the HyperBac Control Service for compression/decompression

About SQL Storage Compress encrypted databases

Although not shown in the example above, SQL Storage Compress can also be used to encrypt database files. The encryption/decryption processing is performed by the HyperBac Control Service in exactly the same way as compression/decompression processing. The SQL Server instance remains unaware that the database files are encrypted.

Note: if you choose to encrypt a transaction log file, this will also compress the file. This can result in decreased performance for transaction-intensive databases.

Reported 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 data file, you must check file sizes directly (for example, using Windows Explorer, or with the database browser in the SQL Storage Compress application).

How SQL Storage Compress creates compressed databases

To create a compressed version of an existing database, you restore from a backup file, using filename extensions that SQL Storage Compress recognizes.

During the restore process, SQL Storage Compress intercepts the data that SQL Server writes out to disk, ensuring that the restored data files are compressed. SQL Storage Compress uses file extensions *.mdfx (primary data file) and *.ndfx (secondary data file) for the compressed data files. Transaction log files are not compressed by default.

The illustration shows SQL Storage Compress creating a compressed version of the "Sales" database from a native SQL Server backup file ("salesdb.bak"). The new transaction log file for the compressed database is omitted for clarity:

SQL Storage Compress uses a standard RESTORE DATABASE T-SQL statement to create a compressed database.

You can generate and run the T-SQL RESTORE DATABASE statement needed to create a compressed database:

• automatically, using the SQL Storage Compress application wizard

This is the easiest way to create compressed databases. You don't need to write T-SQL, and can also review the disk space savings you could achieve. The application supports restoring from backup files in the following formats: native SQL Server, SQL Backup Pro, SQL HyperBac, and Quest LiteSpeed™ (unencrypted). Read more

• manually, using SQL Server Management Studio, or your preferred SQL Server management tool

Writing the T-SQL RESTORE DATABASE statement manually gives you complete control over all statement options. You will need to use this method if you are restoring from a backup file that was created by a third-party backup tool; any required third-party components must also be installed. Read more

Once the restore process is complete, compressed databases are available to use immediately.