Compressing Encrypted Backups

TSQL2sDayA common requirement, whether it be based out of pure want or truly out of necessity, is to make a large database backup file, that is encrypted, be much smaller.

This was a knock for the early days of Transparent Data encryption (circa SQL Server 2012). If TDE were enabled, then a compressed backup (though compression was available) was not an option. Not only did compression in the 2012 implementation of TDE make the database backup not smaller, it occasionally caused it to be larger.

This was a problem.  And it is still a problem if you are still on SQL 2012. Having potentially seen this problem, amongst many others, Ken Wilson (blog | twitter) decided to ask us to talk about some of these things as a part of the TSQL Tuesday Blog party. Read all about that invite here.

Encrypted and Compressed

dbsecurityWell, thankfully Microsoft saw the shortcoming as well. With SQL Server 2014, MS released some pretty cool changes to help us encrypt and compress our database backups at rest.

Now, instead of a database backup that could potentially get larger due to encryption and compression combined, we have a significant hope of reducing the encrypted backup footprint to something much smaller. Here is a quick example using the AdventureWorks2014 database.

In this little exercise, I will perform three backups. But before I can even get to those, I need to ensure I have a Master Key set and a certificate created. The encrypted backups will require the use of that certificate.

Do this in a sandbox environment please. Do not do this on a production server.

In the first backup, I will attempt to backup the AW database using both encryption and compression. Once that is finished, then a backup that utilizes the encryption feature only will be done. And the last backup will be a compressed only backup. The three backups should show the space savings and encryption settings of the backup if all goes well. The compressed and encrypted backup should also show an equivalent savings as the compression only backup.

With that script executed, I can query the backup information in the msdb database to take a peek at what happened.

This should produce results similar to the following:


Looking at the results, I can see that the compression only backup and the compression with encryption backup show very similar space savings. The compression only dropped to 45.50MB and the Compression with encryption dropped to 45.53MB. Then the encryption only backup showed that, interestingly, the CompBackSizeMB (compressed_backup_size) got larger (which is the actual size on disk of this particular backup).

At any rate, the compression now works with an encrypted backup and your backup footprint can be smaller while the data is protected at rest. Just don’t go using the same certificate and password for all of your encrypted backups. That would be like putting all of your eggs in one basket.

With the space savings available in 2014, and if you are using SQL 2014, why not use encrypted backups?

Data Compression

We have come to that time of month again – blog party.  This time, the party is hosted by Jorge Segarra (aka SQLChicken) (Blog | Twitter).  The rules have also changed this month, though ever so slightly.  Now, we must insert a pic into our blog post rather than include the TSQL Tuesday in our blog title.  BTW, this month we are discussing features in SQL 2008 (supposed to be R2, but I am only discussing something that came out with 2008).

Pick your Poison…err Feature.

The feature that I have chosen is compression.  In SQL 2008 we have the option to compress backups as well as compress the data.  The data can be compressed in two methods as well.  I will just be discussing my experience with Page level compression.  I will touch lightly on the differences between row level and page level compression.


Do you recall a nifty utility that Microsoft gave us back in the glory days called Doublespace that was later renamed to Drivespace?  I do!!  Oh the memories are painful still.  That little compression agent was renamed due to the inaccuracy of the name.  You didn’t truly get double the space on your hard drive by using it.  I remember numerous support calls related to compression and all of them turned out ugly.  Something about compressing your drive and then losing everything because you used a Drive Overlay to access a larger disk drive than the BIOS supported and then used Doublespace to compress it.  Or another good one was to lose the doublespace bin files from a compressed drive.  You could also see heavy fragmentation issues.  All of these have created a heavy bias for me against mass compression utilities.

Ch Ch Ch Changes

Despite my heavy bias against compression, I have always liked the ability to compress selectively certain files or folders.  The reasons for compressing in this method, for me, have largely been for archival purposes.  There are several file compression utilities out there on the market for use in performing this.

How does that relate to database compression?  I see database compression, as offered with SQL 2008, to be more like these file compression utilities than DriveSpace.  Data compression in SQL 2008 is not an all or none implementation.  You get to pick and choose what gets compressed.  That is a big time bonus for me.

The Setup

After some research and having learned a bit about compression, I decided to test it out.  I have yet to test performance as I have only tested the disk savings that compressing could generate.  There is a good demonstration on performance by Jason Shadonix here, for those that are interested.  I will be baselining and testing performance at a later time – that is just a bit beyond the scope for this article.

I decided to use a database from our warehouse that resides on SQL 2000, on Windows 2003 32 Bit, currently.  I created a backup of that database and restored it to a test box that is running SQL 2008 on Windows 2008 R2 64 Bit.  The starting database size was 164GB.  The database was also left in SQL 2000 compatibility mode.  The selection criteria for tables to compress was to select any table larger than 1GB in size.  I used a script I showed in the table space series to determine which tables to target.  The script can be found as follows.

In this script, you will note that I am dumping the results into a temp table.  I wanted to be able to run a few different cases against the data to check results quickly without running the entire query over again.  Though, running this particular query would not take that long in most cases.  Consider the temp table a fail-safe to prevent long execution times of the query.

To determine which tables from the previous query I would target, I employed a script similar to the following.

This is pretty straight-forward.  I am only selecting those tables from the temp table that are larger than 1 GB.  I am also concatenating a string to those tables for manual execution purposes.  I can now see which tables are “large” and compress them with Page level compression or not (note: the same sort of script would work for Row Level compression).  After, I was happy with the result set and which tables would be compressed, I proceeded with compressing the tables.  Recall that the database started at 164GB.  After compression of the handful of tables (30 versus the 460 tables in the database), the database used space was now down to 84GB.  That is a reduction of ~49% in space requirements for this database.  For me, that is a pleasing result.

What if…

Should you decided to run this process again, the tables that you have already compressed will be included in the result set.  Should you proceed with running the table alter scripts again, you will not get any better compression and the compression will remain in effect.  Or, you could eliminate those tables that are compressed by altering the script provided to also read from sys.partitions as in the following script.


Without hard and fast numbers concerning performance, a final conclusion could not be made concerning whether to compress or not.  Having run a few dry runs to test if processes still worked, I am pleased with the performance (it is faster – I don’t have the final data to back that now though).  The disk savings and even with a stalemate on performance, I like the compression that is offered in SQL 2008.

page 1 of 1

February 2020
« Jan    

Welcome , today is Saturday, February 22, 2020