Guide for evaluators - SQL Storage Compress

This guide is for first-time users and evaluators of SQL Storage Compress and covers:

1. Installing SQL Storage Compress
2. Analyzing potential space savings
3. Compressing databases with the SQL Storage Compress wizard
4. Viewing achieved space savings
5. Activating SQL Storage Compress
6. Converting a compressed database to a native SQL Server database

For information on evaluating the performance of SQL Storage Compress, see the Performance evaluation kit.

Installing SQL Storage Compress

Important: if the HyperBac Control Service is already installed, follow the instructions for Upgrading SQL Storage Compress.

You install SQL Storage Compress in two parts: the HyperBac Compression Engine Components first, followed by the SQL Storage Compress user interface.

1. Download SQL Storage Compress from the Red Gate website.
2. Extract the contents of the downloaded zip file to the server that will host your compressed databases.
3. Open Step 1_HyperBac Engine and install the HyperBac Compression Engine Components:
   • for 64-bit machines, run HyperBac_Installer_x64.exe
   • for 32-bit machines, run HyperBac_Installer_x86.exe

   The HyperBac Compression Engine Components are required to create and use SQL Storage Compress databases. The components include a Windows service (the HyperBac Control Service), and several utilities (HyperBac Configuration Manager, HyperBac WinExtractor, and the HyperUtil command line utility).

   By default, the HyperBac Compression Engine Components are installed in %ProgramFiles(x86)%\Red Gate\HyperBac on 64-bit machines and %ProgramFiles%\Red Gate\HyperBac on 32-bit machines.

4. Open Step 2_SSC UI and run SQL_Storage_Compress_6.0.exe

   This installs the SQL Storage Compress user interface, which 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.

5. You have a 14 day trial period in which to evaluate SQL Storage Compress. You can see the number of days left in your evaluation period from the SQL Storage Compress user interface. If you need more time to complete your evaluation, please email dba.info@red-gate.com.

For more detailed information about installing SQL Storage Compress, see Installing and activating SQL Storage Compress.

Analyzing potential space savings

You can use the SQL Storage Compress user interface to review the space currently occupied by user databases on local SQL Server instances, and to estimate how much disk space you could save by compressing these databases.

Viewing current database sizes

The SQL Storage Compress user interface automatically searches for local SQL Server instances when it runs. These instances are listed on the Database sizes tab.

If a local instance is not added automatically, click Add a local instance in the toolbar. You can only browse for instances running on the same server as the SQL Storage Compress user interface.

To remove an instance from the list or change the connection credentials, right-click the instance and select the appropriate command.

Only the user databases on each instance are listed. You can compress system databases if required, but these are usually too small to generate worthwhile space savings.

Viewing potential savings

On the Summary of savings tab, SQL Storage Compress estimates the total space savings you could make by compressing all the user databases on the instances listed in the Database sizes tab.

SQL Storage Compress estimates potential disk space savings by assuming that all data files for each database will be compressed, and that each uncompressed database will be compressed by 80%. The potential savings figure is an estimate and the actual compression achieved will depend on the type of data in your database.

Compressing databases with the SQL Storage Compress wizard

The SQL Storage Compress wizard guides you through the process of creating a compressed database by restoring from a backup.

When using the wizard, the data files are compressed but the transaction log file is not (unless you select encryption; see step 7 below). This is because the transaction log file generally represents a small proportion of the storage footprint, so compression offers little benefit. Furthermore, in transaction-intensive databases, with frequent reads and writes to the log file, compressing the log file can impair performance.

To restore to compressed data and log files or to compress only particular data files, you will need to write your own T-SQL script. You can use the wizard to generate a script which you then edit and run manually.

1. Take a backup of the database you want to compress. The wizard supports native SQL Server, SQL HyperBac, SQL Backup and (unencrypted) Litespeed backup files.
2. From the SQL Storage Compress user interface, click the Restore backup to compressed database button to open the wizard.

   

3. On step 1 of the wizard, select the instance that you want to restore your compressed database to.
4. Select the full backup of the database you want to compress.

   For more information on requirements for restoring from different backup formats see Using the wizard to create compressed databases from backup files.

   

   To create a compressed database by restoring from a combination of backup file types, such as full, differential and transaction log backups, you will need to write your own T-SQL script.

5. On step 2 of the wizard, specify a name for the compressed database. By default, this is the name of the backed-up database followed by _SSC.

   SQL Storage Compress does not automatically delete the original database. To overwrite the original database, change the name to match the original and select If a database with this name exists, use WITH REPLACE to overwrite it.

   

6. Specify the location for the compressed database files.
7. You can choose to encrypt your database files. If you select encryption, the transaction log file will also be encrypted and compressed. Compressing the log file can impair performance on transaction-intensive databases.
8. The wizard also provides the option to run a database integrity check (DBCC CHECKDB) after the restore has completed.
9. On step 3 of the wizard, you can view a summary of the options you have selected and the T-SQL script that SQL Storage Compress will run. You can copy or save the script to edit and run manually later.

   

10. Click Finish to restore the database to compressed data files (or compressed and encrypted data and log files). You can view and copy the script that was run from the Script tab.

    

For more information about using the wizard, see Using the wizard to create compressed databases.

Viewing achieved space savings

Once you have created a compressed database, it is added to the list in the Database sizes tab and details of the space savings are shown on the Summary of savings tab.

Viewing current database sizes

Uncompressed databases, including the original AdventureWorks database that was backed up, are shown with a yellow icon and their original (current) size.

The new, compressed database, AdventureWorks_SSC, is shown with a blue icon along with its original (uncompressed) size, compressed size and space savings.

The original AdventureWorks database still exists because it was not overwritten as part of the restore process. Once you are happy that the compressed version is working as expected, you can drop the database to achieve the space savings.

To view the size of individual database files, click the arrow to the left of the database icon to expand the row.

Note: SQL Server is unwaware of the compression provided by SQL Storage Compress. As a result, SQL Server always displays the uncompressed file sizes. To see the actual, compressed file sizes, use the SQL Storage Compress user interface as described above, or view the files directly in Windows Explorer. You can do this from the user interface by right-clicking the database file and selecting Locate file on disk.

Viewing current and potential savings

The Current vs potential size graph compares the amount of space currently used by all user databases on the instances listed in the Database sizes tab to their original size and potential size. In the screenshot above, the original AdventureWorks database has been deleted to realise the space savings.

•   Original size: the total size for all database files, if no databases were compressed.
•   Current size: the actual total size for all database files, including compressed database files.

  The difference between Original size and Current size is equal to Current space saved.

•   Potential size: the estimated total size for all database files, if all databases were compressed by 80%.

  The difference between Original size and Potential size is equal to Total potential space savings (estimated).

Note: if SQL Storage Compress cannot access a database file, it considers its size to be the same as the size reported to SQL Server; therefore, no savings are reported. It may be that the file is in fact compressed, but these savings cannot be shown. Database files may be inaccessible to the SQL Storage Compress user interface if:

• your current login does not have permissions to read a database file on disk
• the instance is clustered, and the current node does not have access to the disk

Activating SQL Storage Compress

1. To purchase a SQL Storage Compress license or for information about pricing, contact dba.info@red-gate.com.
2. Once you have purchased a license, use your serial number to activate the product. This can be found on your invoice or by going to www.red-gate.com/myserialnumbers.
3. From the SQL Storage Compress user interface click Activate license or from the Help menu select Enter serial number to open the Activate SQL Storage Compress dialog.

For information about activating, see Activating SQL Storage Compress.

Converting a compressed database to a native SQL Server database

If your has trial expired and you need to access compressed (or compressed and encrypted) database files but do not want to purchase a SQL Storage Compress license, you can convert the files to native format using HyperBac WinExtractor (a Windows program) or HyperUtil.exe (a command line program). You do not need a SQL Storage Compress license to use these utilities.

Before you start, make sure that you know where the compressed database files are located and that HyperBac WinExtractor or HyperUtil.exe is available on the SQL Server.

• HyperBac WinExtractor is copied to %ProgramFiles%\Red Gate\HyperBac\gui on 32-bit machines and %ProgramFiles(x86)%\Red Gate\HyperBac\gui on 64-bit machines by default.
• HyperUtil.exe is copied to %ProgramFiles%\Red Gate\HyperBac\bin on 32-bit machines and %ProgramFiles(x86)%\Red Gate\HyperBac\bin on 64-bit machines by default.

Using HyperBac WinExtractor

1. Take the compressed database offline. Either:
   • Right-click each database in SQL Server Management Studio (SSMS), and select Tasks > Take Offline. You may need to run this task twice before SSMS reports the database as being offline.

   or,

   • Ensure you are connected to the master database, then run the following T-SQL statement for each database:

     ALTER DATABASE <database name> SET OFFLINE

     You may need to run this statement twice before SQL Server reports the database as being offline.

2. Open HyperBac WinExtractor.
3. Click the browse button next to Source File (HyperBac Archive) and choose the database file you want to convert. Compressed database files use the extensions .mdfx, .ndfx and .ldfx. Encrypted and compressed database files use the extensions .mdfe, .ndfe and .ldfe.
4. Click the browse button next to Destination File and specify a name and location for the converted database file.
5. If the database file is encrypted (i.e. if it uses the extensions .mdfe, .ndfe or .ldfe), click the browse button next to Encryption Key File and specify the key file that was used to encrypt the file. See Working with key files for encrypted data for more information.
6. Click Extract to create an uncompressed, native SQL Server version of the compressed database file.

   

7. Repeat steps 3-6 for each of the compressed database files.
8. You can use the converted database files to create a standard SQL Server database. For example:

   CREATE DATABASE [AdventureWorks_Orig] ON PRIMARY
( NAME = N'AdventureWorks_Orig', FILENAME = N'D:\Backups\AdventureWorks_Data_Orig.mdf' )
LOG ON
( NAME = N'AdventureWorks_Orig_Log', FILENAME = N'D:\Backups\AdventureWorks_Log_Orig.ldf' )
FOR ATTACH

9. The compressed database files are not automatically deleted. Once you have verified that the new database and converted files are working as expected, you can drop the compressed database.

Using HyperUtil.exe

1. Take the compressed database offline. Either:
   • Right-click each database in SQL Server Management Studio (SSMS), and select Tasks > Take Offline. You may need to run this task twice before SSMS reports the database as being offline.

   or,

   • Ensure you are connected to the master database, then run the following T-SQL statement for each database:

     ALTER DATABASE <database name> SET OFFLINE

     You may need to run this statement twice before SQL Server reports the database as being offline.

2. Open a Command Prompt window in the HyperBac 'bin' folder (hold CTRL + SHIFT, right-click and select Open command window here). Alternatively, open a Command Prompt window anywhere and change directory to the HyperBac 'bin' folder (for example, cd C:\Program Files\Red Gate\HyperBac\bin).
3. Type the following command and press ENTER:

   HyperUtil /S"<compressed_database_file>" /O"<destination_file>" /E

   where <compressed_database_file> is the full path of the database file you want to convert, and <destination_file> is the full path of the converted database file you will create. For example:

   HyperUtil /S"C:\Program Files\MSSQL\DATA\AdventureWorks_SSC_AdventureWorks_Data.mdfx" /O"C:\Program Files\MSSQL\DATA\AdventureWorks_Data.mdf" /E

   If you have encrypted the database file, you will also need to include the /K argument and specify the full path for the key file that was used to encrypt it. For example /K"C:\Program Files\Red Gate\HyperBac\keys\AES_256.key". See Working with key files for encrypted data for more information.

   HyperUtil.exe prints metadata about the compressed file to the Console Window then creates an uncompressed, native SQL Server version of the compressed database file in the specified location.

4. Repeat step 3 for each of the compressed files.
5. You can use the converted database files to create a standard SQL Server database. For example:

   CREATE DATABASE [AdventureWorks_Orig] ON PRIMARY
( NAME = N'AdventureWorks_Orig', FILENAME = N'D:\Backups\AdventureWorks_Data_Orig.mdf' )
LOG ON
( NAME = N'AdventureWorks_Orig_Log', FILENAME = N'D:\Backups\AdventureWorks_Log_Orig.ldf' )
FOR ATTACH

6. The compressed database files are not automatically deleted. Once you have verified that the new database and converted files are working as expected, you can drop the compressed database.

If you need any assistance with your evaluation of SQL Storage Compress, please contact dba.info@red-gate.com.