SQLSweet16!, Episode 1: Backup Compression for TDE-enabled Databases
Sanjay Mishra, with contributions from Aasav Prakash
Reviewed by: Denzil Ribeiro, Murshed Zaman, Arvind Shyamsundar, Mike Ruthruff
Celebrating the release of SQL Server 2016, we are starting a new blog series on exciting new scenarios and features. Aptly named “Sweet 16” series, we will discuss sixteen new capabilities in SQL Server 2016 (one on each post) that help improve your business, and enhance your life as database professionals. Among the ocean of new capabilities, we have chosen 16, so will we will miss out on some very good ones. If we miss out your favorite ones, and you would like to hear from us, let us know and we will try our best to cover.
Starting with a hidden-gem: Backup compression for TDE-enabled databases.
Backup Compression and Transparent Data Encryption (TDE) have been two immensely valuable and popular features in SQL Server. Both were released as part of SQL Server 2008. However, had very little co-existence till now. While backup compression worked great for databases that were not enabled for TDE, its effectiveness for TDE-enabled databases was very limited. We described this behavior in our SQL Server 2008 blog post (please refer to pages 22-23 in our old blog collection document), and recommended not to use backup compression for TDE-enabled databases.
These two wonderful features could not stay strangers to each other for long. Starting with SQL Server 2016, you can now get the benefits of backup compression for TDE-enabled databases. Sweet!
This is one of the least publicized features in SQL Server 2016, and has managed to remain under the radar.
Here are some numbers to get you interested. I performed some tests with a database of about 115 GB size (real world data, not synthetic test data) first on SQL Server 2014, and then on SQL Server 2016 on the same server. The results are very impressive on SQL Server 2016.
[caption id="attachment_2765" align="alignnone" width="1417"]
Figure 1: Backup Compression with TDE (SQL Server 2014)[/caption]
As illustrated in Figure 1, in SQL Server 2014 (and prior versions) backup compression doesn’t help reduce the backup size of a TDE-enabled database. However, due to the time spent in attempting the compression, the backup takes much longer to complete.
Things change completely in SQL Server 2016. You not only get great compression of the backup file size, but the backup time is reduced significantly as well, illustrated in Figure 2.
[caption id="attachment_2755" align="alignnone" width="1299"]
Figure 2: Backup Compression with TDE (SQL Server 2016)[/caption]
The performance numbers presented here are examples. Your mileage will vary based on your data, workload and hardware.
Important to Know
It is important to know that while backing up a TDE-enable database, the compression will kick in ONLY if MAXTRANSFERSIZE is specified in the BACKUP command. Moreover, the value of MAXTRANSFERSIZE must be greater than 65536 (64 KB). The minimum value of the MAXTRANSFERSIZE parameter is 65536, and if you specify MAXTRANSFERSIZE = 65536 in the BACKUP command, then compression will not kick in. It must be “greater than” 65536. In fact, 65537 will do just good. It is recommended that you determine your optimum MAXTRANSFERSIZE through testing, based on your workload and storage subsystem. The default value of MAXTRANSFERSIZE for most devices is 1 MB, however, if you rely on the default, and skip specifying MAXTRANSFERSIZE explicitly in your BACKUP command, compression will be skipped.
Update Sep 20th, 2017
The fixes for issues described below have been released in SQL Server 2016 RTM CU7 and SQL 2016 SP1 CU4. If you plan to leverage native backup compression for TDE databases or are already using it, we strongly recommend applying the latest CUs on SQL 2016 to ensure you are not hitting any of the known issues we have discovered earlier. It is important to know VDI support for backup compression on TDE enabled databases is not added yet and we plan to add it soon in upcoming servicing releases of SQL Server.?
Update April 6th, 2017
We have recently discovered some issues related to the use of TDE and backup compression in SQL Server 2016. While we fix them, here are some tips to help you avoid running into those known issues:
- Currently it is not advisable to use striped backups with TDE and backup compression
- If your database has virtual log files (VLFs) larger than 4GB then do not use backup compression with TDE for your log backups. If you don’t know what a VLF is, start here.
- Avoid using WITH INIT for now when working with TDE and backup compression. Instead, for now you can use WITH FORMAT.
SQL engineering is working on fixes for these issues in SQL Server 2016. We will update this blog post once again once we have further information to share.
My Wish List
My wish list is that backup compression would work for TDE-enabled databases, irrespective of MAXTRANSFERSIZE setting. Specifying MAXTRANSFERSIZE in the BACKUP command may be a simple thing for many, but may be a bit cumbersome for some. Many DBAs rely on scripts generated from SSMS, which doesn’t specify MAXTRANSFERSIZE, and some DBAs who craft their own BACKUP scripts rely on the default value of MAXTRANSFERSIZE. Moreover, the log shipping UI doesn’t provide an option to specify MAXTRANSFERSIZE. It would be nice, if backup compression worked for all, without having to rely on explicitly specifying MAXTRANSFERSIZE.
What is on your wish list?
Call to Action
Many of you have asked for this capability for years. And, now that it is in the product, I urge you to go and test with your databases (especially the larger ones), and share some of your findings – compression ratio, backup time, backup size, etc.