Skip to main content
Delphix

Configuring Compression in Delphix Managed Backups (KBA3799)

 

 

KBA

KBA#3799

Overview

Since Delphix Engine 5.2, the Delphix Engine has allowed compression to be enabled ("forced") for SQL Server backups which use Delphix Managed Backups.

Delphix Managed Backups are used to synchronize SQL Server dSources when existing SQL Server backup files cannot be made available to the Staging server, or if a third-party backup vendor is used that is not yet supported by Delphix.

For more information on this functionality see Delphix as a Backup Solution to SQL Server and Linking a dSource with SQL Server.

Default Behavior of Delphix Managed Backups

By default, this method of synchronizing dSources takes the following approach, which may not compress the SQL Server backup:

  • An iSCSI disk is mounted on the Staging Server, using storage supplied by the Delphix Engine
  • This disk is shared via Windows File Sharing (SMB)
  • The Source database server is directed to take a backup of the Source database to this shared folder:
BACKUP DATABASE [Database Name] TO DISK='\\STAGINGSERVER\NewSharedFolder\dlpxcopyonlybackup.bkp' WITH COPY_ONLY
  • The Staging database restores the database from this backup, also to storage supplied by the Delphix Engine
  • The Staging copy of the database is used to create a Database Snapshot

This behavior will compress backups in accordance with the default backup compression settings for the Source SQL Server instance, as described in Microsoft's document View or Configure the backup compression default Server Configuration Option.

Effects of Enabling Backup Compression

Without backup compression, a 1TB database will require the following resources during a Delphix Managed Backup.

  • 4TB of network traffic

    • 1TB from the Source to Staging servers during the backup to the Windows File Share

    • 1TB from the Staging server to the Delphix Engine, to write the backup over iSCSI

    • 1TB from the Delphix Engine to the Staging server, to read the backup file during the RESTORE operation

    • 1TB from the Staging Server to the Delphix Engine, to create the restored database

  • Up to 2TB of disk space on the Delphix Engine (the Delphix Engine will perform some compression internally)

    • Up to 1TB to hold the temporary backup file

    • Up to 1TB to host the Staging copy of the database for snapshots

 

If backup compression is able to achieve a 5:1 compression ratio, this significantly reduces the network overhead:

  • 1.6TB of network traffic

    • 200GB from the Source to Staging servers during the compressed backup to the Windows File Share

    • 200GB from the Staging server to the Delphix Engine, to write the backup over iSCSI

    • 200GB from the Delphix Engine to the Staging server, to read the backup file during the RESTORE operation

    • 1TB from the Staging Server to the Delphix Engine, to create the restored database

  • Up to 1.2TB of disk space on the Delphix Engine (the Delphix Engine will perform some compression internally)

    • Up to 200GB to hold the temporary backup file

    • Up to 1TB to host the Staging copy of the database for snapshots

Reasons to Use Backup Compression

Backup compression is preferable in the following situations when:

  • The default backup compression setting for the Source SQL Server instance is Disabled (0)

  • The Source database does not use Transparent Data Encryption (TDE)

  • The Source server has available CPU resources to perform compression

Where the compression ratio of a compressed backup exceeds 3:1, this will reduce the amount of data (by half) that must be transferred over the network to perform a backup, resulting in much faster SnapSync operations.

Limitations of Using Backup Compression

Backup compression will consume additional CPU resources on your Source SQL Server host during the backup operation. For CPU-bound databases, this may be a concern, and should be discussed with your database administrators.

Note that the compression ratio achieved by backup compression will depend a great deal on your source database. Databases are likely to see little benefit from compressed backups when they contain mostly encrypted or compressed data or which use Transparent Data Encryption.

For current guidance on using compressed backups see to Microsoft's documents on backup compression in SQL Server.

How to Enable Backup Compression

If the SQL Server instance's default backup compression setting is enabled (see Microsoft's document View or Configure the backup compression default Server Configuration Option), no specific action is required. Backups will automatically be compressed in accordance with this setting.

If backups are not automatically compressed, the Delphix Engine (5.2 and later) can be configured to force compressed backups using the following steps:

  • Login to the Delphix Engine's Management interface.

  • Open the Manage > Datasets screen.

  • Locate and select the dSource (using the tree on the left).

  • Navigate to the Configuration > Data Management pane.

  • Click the Edit (pencil) icon to modify the Validated Sync settings.

image-20190330135151950.png

  • Select the Force Compression checkbox, then the select the Tick icon to save the changes

image-20190330135259079.png

The change will automatically take effect during the next scheduled Snapshot/SnapSync operation.