Why Compressing Database Backups Makes Good Sense

by Dec 16, 2019

Admittedly, it would be nice if all members of the IT team had the same goals when performing their jobs. While the overriding concern of all parties is the maintenance and support of the systems that comprise the computing environment, there may be discrepancies in the tactics that various teams would like to employ. There are often valid arguments presented to bolster opposing viewpoints, making it difficult to determine the correct course of action.

One area of possible contention which impacts multiple teams is the way database backups should be handled. An enterprise’s DBAs and storage administrators both have a stake in how backups are performed. Advanced storage appliances that can perform file deduplication may have been introduced into the environment, which in some cases can result in saving substantial amounts of storage space. Unfortunately, deduplication is not the best way to go with all types of data.

Problems Associated with Deduplicating Database Backups

Database backups are an example where deduplication may lead to degraded performance when compared to compressing the data on its server. This is definitely a counter-intuitive outcome, as your first thought might be that any way to save storage space will benefit your backup procedures. You would be mistaken due to the way deduplication works and the type of data that benefits from going through the process.

There are many deduplication appliances on the market which all work using the same general methodology. Data is sent from a server to the appliance where it is deduplicated before undergoing further processing, such as being backed up. This implies that the complete original file is transferred across the network from the server to its deduplicating destination. So while storage space is reduced, there are no comparable savings in time transferring the file.

Deduplication also does not play particularly well with encrypted data. The process works by eliminating duplicate blocks of data which are often obscured during the encryption process. Attempting to trim the size of an encrypted database backup will, in all likelihood, result in minimal space-saving at the cost of the time spent in the reduction effort.

File deduplication is most effective when dealing with uncompressed data such as that generated by file servers. When working with database backups, a different strategy may make more sense. DBAs need to balance the wise use of storage capacity with the time constraints enforced when backing up critical databases. Backups that are not completed in a timely manner may be useless when needed for a restore.

Deduplication can adversely affect both the database backup and restore process by sending more data through your network pipes than necessary. Compressing the data before backing it up will result in smaller files that are transferred to the backup media more quickly. Encrypting is an essential step in protecting the data which an organization needs to survive.

Efficiently Compressing and Encrypting Your Database Backups

IDERA’s SQL Safe Backup is a software tool that enables you to backup, restore, and create virtual databases from SQL Server instances located on-premises or in the cloud. It incorporates IntelliCompress2 technology that automatically adjusts compression parameters to create the smallest backup files in the least amount of time based on the current state of your environment. Advanced 128-bit and 256-bit AES encryption protect your data with a performance hit of less than 0.5%.

Backups can be automated and managed with policies to eliminate the need for manual scripts. A central repository stores backup and restore information to enable the backup history to be fully audited. Multiple interfaces including a web console, desktop console, and commanding interface are available to facilitate using the tool at any time. Customizable alerting and reporting capabilities keep you updated on problems impacting your database backups.

An IDERA whitepaper on this subject is available and provides further details regarding the enhanced performance of database backups and restores when using compressed data. DBAs should give it a read if they are concerned with the performance of database backups and need some ammunition when consulting with the enterprise’s storage admins. No need for fisticuffs, just lay out the facts. Let the storage team deduplicate to their heart’s content as long as they leave your database backups alone!