Installing SQL Storage Compress on a cluster

To install SQL Storage Compress on a cluster:

1. Install and activate the HyperBac Compression Engine Components and SQL Storage Compress user interface on each node of the cluster
2. Configure the SQL Storage Compress index files
3. Configure the SQL Storage Compress key files (for encryption)
4. Make the HyperBac Control Service a clustered resource

Installing SQL Storage Compress on each node

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

Installing the HyperBac Compression Engine Components

Install the HyperBac Compression Engine Components on each node of the cluster as follows:

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. If you have already purchased a SQL Storage Compress license, select the option to open the Activation Dialog for HyperBac Tools to activate your license. See Activating SQL Storage Compress for more information.

Installing the SQL Storage Compress user interface (optional)

The SQL Storage Compress user interface 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. Open Step 2_SSC UI and run SQL_Storage_Compress_6.0.exe on each node that you want to install the user interface on.

Configuring the SQL Storage Compress index files

The HyperBac Compression Engine Components create and maintain indexes for SQL Storage Compress databases. By default, these indexes are located in %ProgramFiles(x86)%\Red Gate\HyperBac\indexes on 64-bit machines and %ProgramFiles%\Red Gate\HyperBac\indexes on 32-bit machines.

• In an active/active cluster where both SQL Server instances will be using the HyperBac Control Service, the indexes must be stored on disks local to each node.

  In the event of any type of failover, the indexes will need to be rebuilt. This can take some time as the entire data file may need to be scanned if the database was in use at the time of the failover.

• In an active/passive cluster or an active/active cluster where only one SQL Server instance will be using the HyperBac Control Service, the indexes can be stored on a cluster resource disk or on disks local to each node.

  If the indexes are stored on a cluster resource disk, the indexes should not need to be rebuilt in the event of a managed failover. Managed failover times should be comparable with those for a native uncompressed database. However, in the event of a "hard failover", such as a power outage, node crash, or SQL Server crash, the indexes may need to be rebuilt if the database was in use at the time of the failover. If the indexes are stored on disks local to each node, they will need to be rebuilt in the event of any type of failover.

Storing the indexes on disks local to each node

Note: storing the indexes on disks local to each node means that these indexes will need to be rebuilt in the event of any type of failover. This can take some time as the entire data file may need to be scanned if the database was in use at the time of the failover.

To improve performance, the indexes should not be stored on the same physical disk as the SQL Storage Compress compressed databases files (these are files with the extensions .mdfx, .ndfx, .ldfx, .mdfe, .ndfe, and .ldfe).

To change the location of the indexes on a node from the default location to another location on a local disk:

1. Take any existing SQL Storage Compress or SQL Virtual Restore databases offline. To do this:
   • 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.

   Alternatively, stop the SQL Server instance. To do this, right-click the instance in SQL Server Management Studio and click Pause followed by Stop, or stop the service in the Windows Services list.

2. From the system tray, double-click the HyperBac Configuration Manager icon to open the HyperBac Configuration Manager.
3. On the General tab, click Stop to stop the HyperBac Control Service.
4. On the Advanced tab, click the browse button next to the Index Path and select a location.

   

5. Click Save then close the HyperBac Configuration Manager.
6. Copy any existing index files from the original location to the new location.

Repeat these steps on other nodes of the cluster as required. Do not restart the HyperBac Control Service until you have completed the installation. Instructions on restarting the HyperBac Control Service are given at the end of this topic.

Storing the indexes on a cluster resource disk

Note: if the indexes are stored on a cluster resource disk, the indexes should not need to be rebuilt in the event of a managed failover. Managed failover times should be comparable with those for native uncompressed databases. However, in the event of a "hard failover", such as a power outage, node crash, or SQL Server crash, the indexes may need to be rebuilt if the database was in use at the time of the failover.

For performance reasons, the indexes should not be stored on the same physical disk as the SQL Storage Compress compressed database files (these are files with the extensions .mdfx, .ndfx, .ldfx, .mdfe, .ndfe, and .ldfe).

To change the location of the indexes to a location on a cluster resource disk:

1. Take existing SQL Storage Compress or SQL Virtual Restore databases on each of the cluster nodes offline. To do this:
   • 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.

   Alternatively, stop the SQL Server instance. To do this, right-click the instance in SQL Server Management Studio and click Pause followed by Stop, or stop the service in the Windows Services list.

2. On the active node, go to the system tray and and double-click the icon to open the HyperBac Configuration Manager.
3. On the General tab, click Stop to stop the HyperBac Control Service.
4. On the Advanced tab, click the browse button next to the Index Path and select a location on a cluster resource disk.

   

5. Click Save then close the HyperBac Configuration Manager.
6. Open hyperbac.conf using a text editor. By default this file is located in %ProgramFiles(x86)%\Red Gate\HyperBac\bin on 64-bit machines and %ProgramFiles%\Red Gate\HyperBac\bin on 32-bit machines.
7. Locate the entry beginning IndexPath= and copy the whole line to the clipboard.
8. On the passive node(s), go to the system tray and double-click the icon to open the HyperBac Configuration Manager.
9. On the General tab, click Stop to stop the HyperBac Control Service, then close the HyperBac Configuration Manager.
10. Open hyperbac.conf using a text editor.
11. Replace the entry beginning IndexPath=indexes with the line copied from the active node in step 7.
12. Save and close hyperbac.conf.

Do not restart the HyperBac Control Service until you have completed the installation. Instructions on restarting the HyperBac Control Service are given at the end of this topic.

Configuring the SQL Storage Compress key files

If you will not be using encryption on the cluster, you can skip this section.

The HyperBac Compression Engine Components use key files to encrypt and decrypt data. By default, these files are located in %ProgramFiles(x86)%\Red Gate\HyperBac\keys on 64-bit machines and %ProgramFiles%\Red Gate\HyperBac\keys on 32-bit machines.

If any HyperBac operations will use encryption on the cluster (for example, restoring from encrypted SQL HyperBac backups, or restoring to encrypted SQL Storage Compress databases), each node must use the same encryption key.

• In an active/active cluster where both SQL Server instances will be using the HyperBac Control Service, the same key files must be copied to each node.
• In an active/passive cluster or an active/active cluster where only one SQL Server instance will be using the HyperBac Control Service, you can store the key files on a cluster resource disk or copy the key files to each node on the cluster resource disk.

Copying the key files to each node

Key files are created when you create encrypted databases with SQL Storage Compress or encrypted backups with SQL HyperBac. If key files are already present in the keys directory of any of the nodes, copy these to the keys directory of each node so that they are all the same.

If your keys directories are empty, you will need to create a new encryption key before copying it to all nodes. To generate a key, connect to one of the nodes and either:

• Create an encrypted SQL HyperBac backup, using the extension .hbe. For example:

  BACKUP DATABASE model TO DISK = 'C:\test\model.hbe'

or,

• Back up a database to SQL Server native format, then restore it to a new database using the extensions .mdfe, .ndfe and .ldfe for the database files. For example:

  BACKUP DATABASE model TO DISK = 'C:\test\model.bak'

  RESTORE DATABASE model_SSC FROM DISK = 'C:\test\model.bak' WITH MOVE 'modeldev' TO 'C:\test\datafiles\model.mdfe', MOVE 'modellog' TO 'C:\test\datafiles\modellog.ldfe'

The keys directory on the node should now contain a key file. Copy the contents of the directory to the keys directories on the other nodes. You can delete the backup and (if applicable) drop the compressed database.

To change the location of the key files from the default location to another location on the local disk, follow the instructions under 'Storing the indexes on disks local to each node' (above), replacing Index Path in step 4 with Encryption Key Path.

Storing the key files on a cluster resource disk

1. Take any existing SQL Storage Compress or SQL Virtual Restore databases on each of the cluster nodes offline.To do this:
   • 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.

   Alternatively, stop the SQL Server instance. To do this, right-click the instance in SQL Server Management Studio and click Pause followed by Stop, or stop the service in the Windows Services list.

2. On the active node, go to the system tray and double-click the icon to open the HyperBac Configuration Manager.
3. On the General tab, check that the HyperBac Control Service is stopped. If not, click Stop to stop the service.
4. On the Advanced tab, click the browse button next to the Encryption Key Path and select a location on a cluster resource disk.

   

5. Click Save then close the HyperBac Configuration Manager.
6. Open hyperbac.conf using a text editor. By default this file is located in %ProgramFiles%\Red Gate\HyperBac\bin on 32-bit machines and %ProgramFiles(x86)%\Red Gate\HyperBac\bin on 64-bit machines.
7. Locate the entry beginning KeyPath= and copy the whole line to the clipboard.
8. On the passive node(s), go to the system tray and click the HyperBac Configuration Manager icon to open the HyperBac Configuration Manager.
9. On the General tab, click Stop to stop the HyperBac Service, then close the HyperBac Configuration Manager.
10. Open hyperbac.conf using a text editor.
11. Replace the entry beginning KeyPath=keys with the line copied from the active node in step 7.
12. Save and close hyperbac.conf.
13. Copy any existing key files from the original location on each node to the location on the cluster resource disk.

Do not restart the HyperBac Control Service until you have completed the installation. Instructions on restarting the HyperBac Control Service are given at the end of this topic.

See Managing key files for encrypted backups for more information about key files.

Making the HyperBac Control Service a cluster resource

You should make the HyperBac Control Service a cluster resource if:

• you are installing SQL Storage Compress on an active/passive cluster or an active/active cluster where only one SQL Server instance will be using the HyperBac Control Service, and
• you have stored the index files and/or key files on a cluster resource disk.

Windows Server 2003

Use the Cluster Administrator tool to make the HyperBac Control Service a cluster resource:

1. Right-click the SQL Server group, click New, and then click Resource.
2. On the New Resource page, enter the following details:

   Name: HyperBacSrv

   Description: Optional

   Resource type: Generic Service

   Group: The group to which the SQL Server instance is registered.

3. On the Possible Owners page, ensure that the nodes listed match those that the SQL Server instance can run on.
4. On the Dependencies page, you are recommended to add the physical disk.
5. On the Generic Service Parameters page, ensure the Service name is listed as HyperBacSrv.
6. Bring the cluster resource online.

The HyperBac Control Service is now a cluster resource.

You are recommended to make the HyperBac Control Service a dependency of SQL Server. However, this may require the SQL Server resource to be taken offline, so you should wait for a maintenance window to prevent an unnecessary outage.

Windows Server 2008

Use the Failover Cluster Management tool to make the HyperBac Control Service a clustered resource:

1. In the left-hand pane, expand the cluster, then expand Services and Applications.
2. Right-click the SQL Server group, click Add a resource, and then click Generic Service.
3. The New Resource Wizard is displayed.
4. Select HyperBac Control Service from the list then click Next.

   The list is not ordered alphabetically, so you may have to scroll through the entire list to find the HyperBac Control Service resource.

   Note: if the HyperBac Control Service is already clustered, it will not be available in the list.

5. Click Next on the Confirmation page of the wizard.
6. Click Finish on the Summary page of the wizard.
7. The new resource has now been created (offline) and is listed under Other Resources.
8. Under Other Resources select HyperBac Control Service then, on the Actions list click Properties for this resource.
9. The HyperBac Control Service Properties dialog is displayed.
10. Replace the value in Startup parameters with -s.
11. On the Dependencies tab, we recommend that you add the physical disk.
12. On the Policies tab, select the If resource fails, attempt restart on current node option.
13. On the Advanced Policies tab, ensure that all the nodes in the Possible Owners list are selected.
14. Select Apply and OK to confirm the changes.
15. Bring the cluster resource online.

The HyperBac Control Service is now a cluster resource.

You are recommended to make the HyperBac Control Service a dependency of SQL Server. However, this may require the SQL Server resource to be taken offline so you should wait for a maintenance window to prevent unnecessary outage.

Restarting the HyperBac Control Service

If you stopped the HyperBac Control Service in order to configure the index or key files, you will need to restart the service before you can use SQL Storage Compress.

If you made the HyperBac Control Service a cluster resource, bringing the resource online should have restarted the HyperBac Control Service.

If you did not make the HyperBac Control Service a cluster resource or the service did not restart, you can start it from the SQL Storage Compress user interface or right-click the HyperBac Configuration Manager icon in the system tray and select Start HyperBac Control Service. If the HyperBac Control Service does not restart, contact Red Gate Product Support.