Backup Compression for SQL Server TDE Enabled Databases

By: Ahmad Yaseen   |   Comments (2)   |   Related: > Backup

Problem

SQL Server has many great features, but sometimes they don't always work nicely together.  This is the case with Transparent Data Encryption and Backup Compression.  However in SQL Server 2016, Microsoft announced that they can work together and in this tip we take a closer look.

Solution

Transparent Data Encryption (TDE) encrypts the data at rest, which means that TDE performs a real-time I/O encryption and decryption of the SQL Server database data, log and backup files, using a symmetric key that is secured by a certificate stored in the master system database. This key is called the Database Encryption Key or simply DEK.

Database backup compression is important because it enables you to save disk space by generating a backup file smaller than the database size. In addition to saving time required to backup and restore the database.

Before SQL Server 2016 you were not able to use backup compression for TDE enabled databases, but starting with SQL Server 2016 you can get the benefits of backup compression for a TDE enabled database.

Assume that we have the AdventureWorksDW2012_TDE database with TDE enabled on that database, which is hosted in an Enterprise edition of SQL Server 2016. We can query the sys.certificates system view to check the certificate used to encrypt the database:

SELECT name,pvt_key_encryption_type_desc , issuer_name ,expiry_date ,start_date  
FROM sys.certificates

The result in our case is as follows:

Also we will query the sys.dm_database_encryption_keys to list all databases with TDE enabled:

SELECT db_name(database_id) as DATABASE_Name
FROM sys.dm_database_encryption_keys
WHERE encryption_state =3

The databases in our situation are:

Now to make sure TDE is enabled in our database, we will take a full backup for the AdventureWorksDW2012_TDE database twice. The first time without enabling compression and the second time with compression enabled:

BACKUP DATABASE [AdventureWorksDW2012_TDE] 
TO DISK = N'D:\backupSQL\AdventureWorksDW2012_TDE_NotCompressed.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorksDW2012_TDE-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, NO_COMPRESSION,  STATS = 10
GO

BACKUP DATABASE [AdventureWorksDW2012_TDE] 
TO DISK = N'D:\backupSQL\AdventureWorksDW2012_TDE_Compressed.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorksDW2012_TDE-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

Browse the backup location to compare the size of the two files, which shows us that the two generated files have similar sizes.

In order to make backup compression work with TDE-enabled databases, the MAXTRANSFERSIZE backup parameter should be set to a value larger than the default 65536 value. MAXTRANSFERSIZE specifies the largest unit of data transfer in bytes that is used to transfer data between SQL Server and the backup media, with possible values equal to multiples of 65536 bytes (64 KB) and a maximum value equal to 4MB.

So, we will repeat the compressed backup again and set the MAXTRANSFERSIZE to 131072 (128 KB):

BACKUP DATABASE [AdventureWorksDW2012_TDE] 
TO DISK = N'D:\backupSQL\AdventureWorksDW2012_TDE_Compressed.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorksDW2012_TDE-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10, MAXTRANSFERSIZE = 131072
GO

The compressed backup file size equals 20% of the normal uncompressed backup file, which is fantastic.

Also the elapsed time between the compressed and uncompressed backup operations is clear in the following image:

Although you will observe extra CPU cycles consumed to compress the backup, Microsoft allows you to take a compressed backup for a TDE-enabled database. With this you get the benefits of a smaller file and faster backup and restore times.

Next Steps

• Read more about Implementing Transparent Data Encryption in SQL Server 2008
• Check out SQL Server Backup tips

About the author

Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips