Backup Compression for SQL Server TDE Enabled Databases

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

Ahmad Yaseen

Ahmad has a Bachelor’s Degree in Computer Engineering from the University of Jordan and five years of experience working as a SQL DBA, gaining valuable knowledge of database structures, practices, principles and theories. His experience also includes.NET development, working with database applications, scripting and creating SQL queries and views. His personal abilities include having very strong communication and interpersonal skills, the ability to prioritize and to make good sound decisions that benefit the company. He has experience in upgrading, configuring, securing, tuning and monitoring SQL Servers since SQL Server 2005. This includes SQL Server performance tuning, SQL Server resource governor management, SQL Server maintenance plans, SQL Server data collection (Reports) analyzing and SQL databases design, developing, indexing and query optimization. In addition, he is familiar with installing and configuring SSRS, SSIS and SSAS. When it comes to disaster recovery and high availability, he has a solid foundation in SQL backup and recovery scenarios, mirroring, replication, log shipping, SQL clustering and AlwaysOn technology.

• MSSQLTips Awards: Author Contender – 2016-2017 | Trendsetter (25+ tips) – 2016 | Rookie Contender – 2015