A 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

Well, 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.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TSQL2SDay69';
GO
--create our encryptor
USE master;
GO
CREATE CERTIFICATE TSQL2SDay69Cert
WITH SUBJECT = 'TSQL2SDay69 AW2014 Backup Encryption Certificate';
GO
USE master;
GO
BACKUP DATABASE AdventureWorks2014
TO DISK = N'C:\Database\Backup\AdventureWorks2014_compenc.bak'
WITH
  COMPRESSION,
  ENCRYPTION 
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = TSQL2SDay69Cert
   ),
  STATS = 5;
GO
USE master;
GO
BACKUP DATABASE AdventureWorks2014
TO DISK = N'C:\Database\Backup\AdventureWorks2014_enc.bak'
WITH
  ENCRYPTION 
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = TSQL2SDay69Cert
   ),
  STATS = 5;
GO
BACKUP DATABASE AdventureWorks2014
TO DISK = N'C:\Database\Backup\AdventureWorks2014_comp.bak'
WITH
  COMPRESSION,
  STATS = 5;
GO

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.

USE msdb;
GO
SELECT bs.database_name
, bs.backup_start_date
, CASE bs.type
WHEN 'D' THEN 'Full Database'
WHEN 'I' THEN 'Differential Database'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File or Filegroup'
WHEN 'G' THEN 'Differential File'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential Partial'
ELSE 'Unknown'
END AS BackupType
, bmf.physical_device_name
, bs.backup_size / 1024 / 1024 AS BackSizeMB
, bs.compressed_backup_size / 1024 / 1024 AS CompBackSizeMB
, bs.encryptor_type
, bs.key_algorithm
FROM backupset bs
INNER JOIN backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
WHERE bs.backup_start_date > GETDATE() - 1
ORDER BY bs.database_name, bs.backup_start_date DESC;
GO

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?