By: Ahmad Yaseen | Updated: 2016-11-16 | Comments (2) | Backup
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.
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.
- Read more about Implementing Transparent Data Encryption in SQL Server 2008
- Check out SQL Server Backup tips
Last Updated: 2016-11-16
About the author
View all my tips