Note: THERE ARE BUGS IN THIS FEATURE. Make sure you read all the way through the post to catch the updates from Microsoft as more bugs were found.
TDE is one of those things!
You either need it, and quickly learn how many things it plays the devil with, or you don’t need it, and there but for the grace of God go you. Off you go, with your compressed backups, your instant file initialization, your simple restore processes. Sod off, junior.
But Microsoft maybe listened or figured out something by accident. I don’t know which one yet, but they seem excited about it! And I am too! If you read this blog post that’s probably also being monitored closely by Chris Hansen, you’ll see why.
Backup compression now works with TDE
Cool! Great! Everyone encrypt your data and compress your backups. It’s fun. I promise.
Not satisfied with a few meek and meager data points, I set out to see if increasing Max Transfer Size also increased the degree of compression. Why? This paragraph.
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.
It left things open ended for me. Unfortunately for me, it doesn’t help. Fortunately for you, you don’t have to wonder about it.
Check out the exxxtra large screen cap below, and we’ll talk about a few points.
First, the database without a Max Transfer Size at the bottom was a full backup I took with compression, before applying TDE. It took a little longer because I actually backed it up to disk. All of the looped backups I took after TDE was enabled, and Max Transfer Size was set, were backed up to NUL. This was going to take long enough to process without backing up to Hyper-V VM disks and blah blah blah.
The second backup up, just like the blog man said, no compression happens when you specify 65536 as the Max Transfer Size.
You can see pretty well that the difference between compressed backup sizes with and without TDE is negligible.
The most interesting part to me was the plateau in how long each backup took after a certain Max Transfer Size. Right around the 1MB mark, it hits the low 380s, and never strays very far from there afterwards. I could have tried other stuff to make this go faster, but my main interest was testing compression levels.
There you have it
Max Transfer Size doesn’t impact compression levels, but it can help duration. If you want to keep playing with switches, you can throw in Buffer Count, and try striping backups across multiple files to ‘parallelize’ output.
Thanks for reading!
Update (2017/06/16): Bugs!
One of our readers (who’s sharper than us!) noticed that the Microsoft blog post to introduce the new compatibility between TDE and backup compression had an update to it.
Apparently, some combinations of backup options will cause your backups to become corrupted.
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.
So uh, be careful of all that if you’re on 2016 CU5, or 2016 SP1 CU2.
There’s a fix for this in 2016 CU5, and 2016 SP1 CU3.
Happy patching!
Update (2017/09/21): More Bugs & Fixes
There are some scenarios where even the above list of recommendations doesn’t work. The SQL Server Tiger Team explains, and points out that you now need to be on at least 2016 CU7 or SP1 CU4.
39 Comments. Leave new
Any idea how this behaves if the tables are already compressed?
We have to have TDE-enabled databases, so we compress the tables at the index to get the smallest backups possible. I wonder if we’ll see space savings at the backup now also. Something to test if I ever get time.
Yeah, when I compressed everything in SO (page compression), the backup size went down to 76 GB, and the compressed backup size went down to 24 GB. This was after some rather considerable file growth to enable compression.
Very nice! Kevin Farlee mentioned this in his Summit 2015 presentation:
https://www.youtube.com/watch?list=PLoGAcXKPcRvbJ5ge4J2DHSleRy4OjSlJw&v=3KqSerHXlgU#t=65m10s
..and it’s good to see it confirmed. 😉 Privacy regulations are driving us towards encryption for customer data, and I didn’t want to go back to the bad old days of uncompressed backups..
Hi we have serious problems with TDE backups when specifying any value of MAXTRANSFERSIZE.
It always backup with good compression and without errors. But we unable to restore about 30% of our backups.
getting errors like these.
Msg 9004, Level 16, State 3, Line 31
An error occurred while processing the log for database ‘dbname’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Msg 3013, Level 16, State 1, Line 31
RESTORE LOG is terminating abnormally.
2017-02-03 06:10:38.29 spid137 BackupLogIter::GetNextBlock: Block starting with LSN 0x:32b8b5b:800030:1 at 0x2e8736000 bytes offset in file 2 validation status 2.
2017-02-03 06:10:38.29 spid137 Error: 9004, Severity: 16, State: 3.
2017-02-03 06:10:38.29 spid137 An error occurred while processing the log for database ‘dbname’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Dima – we can’t really do Q&A here, but your best bet is to call MS for support.
Hi, I am experiencing same problems but I noticed it immediately because of the restore verify we have on the backups. Backup runs and gets compressed when having TDE and using Maxtransfersize but it seems to be corrupted so you can’t restore them. It looks like there are known issues with this.
See here: https://blogs.msdn.microsoft.com/sqlcat/2016/06/20/sqlsweet16-episode-1-backup-compression-for-tde-enabled-databases/
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.
Holy cow, I gotta add that to the post until they get it fixed. Thanks for sharing!
It looks like this is already fixed if not mistaken. In my case I have the problem when I take backup with compression and use WITH CHECKSUM. If I use WITH NO CHECKSUM the restore works. Note that in both cases the backups completes without errors but you just can’t restore the one having WITH CHECKSUM. Not so good!
Looks like the issue was fixed in last cumulative update released in May. See here: https://support.microsoft.com/en-us/help/4019893
So now I need to perform some patching 🙂
Cool, I’ll add that info in.
New update: I installed the patch last week and unfortunately it didn’t fix the problem. I created a support incident to Microsoft and they have confirmed that there are still some issues with this and they hope to get it fixed in SQL 2016 SP1 CU4. The case is still in progress though.
I think this is quite bad as users may have corrupted backups without knowing. If you don’t have restore verify then you won’t detect the issue because backup is successful but unfortunately the file may become corrupted.
Petur, that’s pretty crazy. Definitely keep us updated!
Even though SQL Server 2016 SP1 CU4 also lists a fix for this in its hotfix list, I just ran into this today on a backup that was taken after we updated to CU4; it looks like there are still some scenarios they haven’t resolved yet. Out of a few dozen databases with TDE that we’ve checked in our environment, this is only happening for the backups from one now, but it is consistently happening for that one. We are using the combination of TDE, checksum, and compression identified as being problematic in https://support.microsoft.com/en-us/help/4019893/fix-restore-fails-when-you-do-backup-by-using-compression-and-checksum.
While trying to reproduce the problem again for Microsoft with a dummy certificate we discovered that disabling and then enabling the TDE encryptiong solved the problem. We also only had some problematic databases while most were working fine. So we disabled and enabled for all problematic and now the backup with compression on TDE databases works. Perhaps you could try that Kevin M. Owen.
I have a question, which I am hoping someone on this BLOG can answer.
In SQL 2012, with TDE turned on, we learned that database backups could no longer be compressed. However, I have found some info online to indicate that it is possible in 2012 to have both TDE and backup compression.
https://www.brentozar.com/archive/2016/07/tde-backup-compression-together-last/
Is this a supported approach by MS to use MS Backup in SQL 2012 and specify MAXTRANSFERSIZE to achieve backup compression?
Hi Will,
No, that isn’t supported in versions prior to 2016 AFAIK.
Anyone know if this got fixed since SQL Server 2016 SP 2 got released? I have tried asking a resident MS SQL PFE but he has yet to get back with me on the subject.
Joie — Not sure about that, but the latest CU for 2017 has some really interesting fixes for it in there.
https://support.microsoft.com/en-us/help/4229789
I don’t know how you were able to get that information so fast! Microsoft’s SQL Server Update Center, the SQL Server Release Services blog nor the ever reliable sqlserverbuilds.blogspot.de had that information yet! What did you push out the update yourself?
Anyway, seems that similar CU fixes for SQL Server 2016 have been released as well.
SQL Server 2016 SP1 CU9
https://support.microsoft.com/en-us/help/4100997/cumulative-update-9-for-sql-server-2016-sp1
SQL Server 2016 SP2 CU1
https://support.microsoft.com/en-us/help/4135048/cumulative-update-1-for-sql-server-2016-sp2
Hoping this fixes all TDE + backup compression issues and not just one specific use case.
Yeah, check out https://sqlserverupdate.com.
Hi, We have recently implemented TDE and backup compression is also enabled. Our database size in 90GB and backup time gone from 20min to one hours after enabling TDE. I have set @MaxTransferSize to 3MB, 2MB, 1MB, 0.5MB and it hasn’t made any difference time wise (consistently about an hour). It is SQL Server 2016 CU4. Any idea why it is taking 3 times longer please? Many thanks
Sajid – for questions, head on over to a Q&A site like https://dba.stackexchange.com.
I got a 101GB down to 28GB using TDE and Compression in SQL 2016 SP2, CU1. I added the following parameters to the Ola Hallengren job step: @blocksize=65536, @BUFFERCOUNT=1024,@MaxTransferSize = 2097152 , @CheckSum = ‘N’,
Just curious if the striped backups with TDE and compression issues were fixed in 2017?
Greg — great question — take a look at recent KB articles for the last couple 2017 CUs and lemme know what you think 😉
Exactly what I was curious about! This one in particular caught my eye.
https://support.microsoft.com/en-us/help/4101502
Even with the latest build of SQL Server 2016 SP2 CU3 (13.0.5216.0), still encountering RESTORE errors on TDE FULL and TRANSACTION LOG database backups generated with the following options:
BACKUP DATABASE [TestDB] TO
DISK = ‘X:\Backup\TestDB_20181008105303_1.bak’
,DISK = ‘X:\Backup\TestDB_20181008105303_2.bak’
,DISK = ‘X:\Backup\TestDB_20181008105303_3.bak’
,DISK = ‘X:\Backup\TestDB_20181008105303_4.bak’
WITH COMPRESSION, FORMAT, MAXTRANSFERSIZE = 1048576, BUFFERCOUNT = 2200
Msg 3287, Level 16, State 1, Line 19
The file ID 1 on device ‘X:\Backups\TestDB_20181008105303_1.bak’ is incorrectly formed and can not be read.
Msg 3013, Level 16, State 1, Line 19
RESTORE DATABASE is terminating abnormally.
I had an active case with Microsoft Support that’s nearly 2 months old now with no resolution -> “[118081618802606] TDE Encypted Database Restores randomly fail following patching of SQL 2016 SP2 CU2 w/4293807 Security update”. I’m basically stuck at SQL 2016 SP2, unable to upgrade my environments due to the restore issues. Any guidance would be appreciated.
Thanks
Keith – no, no idea. Sorry. It’s not something we can troubleshoot or workaround.
MAXTRANSFERSIZE did not work for me on SQL 2017 (CU11) Enterprize, so no compression on a TDE-encrypted database
Nikolay, I found a solution for this btw. The issue was caused by compatibility problems with disks having different physical sector sizes. More specifically, your backup and log file volumes need to be 4k to avoid problems restoring .trn or .bak files that were compressed/TDE. I’ve been using Trace Flag 1800 on our instances since January, and have had no problems since then with SQL Server 2016 CU6. I suspect this will also fix your issues on SQL 2017 .
Trace Flag: 1800
Enables SQL Server optimization when disks of different sector sizes are used for primary and secondary replica log files, in SQL Server Always On and Log Shipping environments. This trace flag is only required to be enabled on SQL Server instances with transaction log file residing on disk with sector size of 512 bytes. It is not required to be enabled on disk with 4k sector sizes
In fact compression did work with TDE-encrypted database when MAXTRANSFERSIZE was well above 64 K, namely it did work with 2 MB, 4 MB, but did not work with 1 MB. SQL Server 2017 incorrectly reports the backup size in msdb.dbo.backupset table showing “uncompressed” size even, when the actual .bak file is compressed and is much smaller. Compression ratio I observed was about 1/5 of the original uncompressed backup size.
Keith, thank you for your input, I will keep it in mind. The reason why I looked into the compressed size and backup performance issue was that our backup engineer reported longer backup completion times after one of the DB machines was encrypted with TDE. Another issue reported by our storage architect is that flash storage compression/deduplication looked liked ruined after one of the machines was TDE-encrypted. Any information about that?
Encrypted data is not really compressible. (That’s why backup compression was such a lifesaver with TDE – the way it works is that the data’s read from disk, decrypted in memory, compressed, and then encrypted again.)
I have tested this with a backup on SQL Server 2019 with a MAXTRANSFERSIZE of 65537 and this compressed the backup with TDE on. This is a single test though and I would be intrigued to know if anyone else has TDE Compressing successfully with TDE Enabled now or if there are still issues in certain scenarios?
Ed – for questions like that, your best bet is a Q&A site like https://dba.stackexchange.com.
Thanks Brent Ozar, this blog post has proven most useful in our decision making and testing of compressed TDE enabled backups.
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. – Is this will applicable to SQL 2017 versions too?
I have achieved the backup compression via max transfer size for full and differential backups.
Now i want to use the max transfer size for the log backups too where the database has VLFs larger than 4GB.
Could you please advice
Easwar – for personal advice, click Consulting at the top of the site.
I’ve tested this a few times today and have not had much luck with really large databases. I have a few over 600Gb, and most are over 200Gb. While the compression does seem to work it only gets down to about 20% smaller than it’s original size, which is lovely, but our specific goal is for file transfer for migration with too many limitations by our “host” to be able to use the MS migration tools.
Hoping someone will see this that has some success with large TDE Databases, would love any insight.
Your best bet there is to post the specifics of your situation at a Q&A site like https://dba.stackexchange.com or https://sqlservercentral.com.