Good morning everyone…
I cannot tell you how excited I am to be posting my first #TSQL2SDay blog. I am an avid follower of the hash tag each month and read all the great blogs that are published. This is the first topic since I went live with my blog in January that I can actually contribute too.
The topic I have chosen is TDE backup compression. This has had an impact on my work environment and with the enhancement to SQL 2016 to allow backup compression again it has accelerated our effort to get 2016 installed.
Early last year our security team started an initiative to encrypt data at rest for all databases that contain PII data. If you do not know what PII data is, it is any data that can used on its own or with other information to identify a persons identity. (Wiki – PII)
Since TDE is so “Transparent” we did not foresee any issues enabling the technology through our development environment and in to production. One downside I did notify the security and storage teams of was that as soon as we enable this we lose all compression on our SQL backups of three main product systems. Our three main product databases that are each 400gb plus in size fall in to the PII category. We currently use SQL native compression as default for all of our backups and we store them on an EMC Isilon X210 network storage device.
After fighting the good fight with security and explaining our concerns from a backup storage perspective I was over ruled and the TDE project would be moving forward.
From my reading I knew that when 2016 was released it was supposed to have a feature to allow backup compression on TDE databases again. So when RTM was available I installed it to a test server and performed some testing.
I found that we were able to get our compression back by setting the MAXTRANSFERSIZE to at least 65537 (64 KB) as outlined in this MSDN blog. My testing yielded the best compression results using MAXTRANSFERSIZE of 131072 (128 KB)
As you can see we will be able to get back to 66% space savings with the new version. I am still not sure why the time to compress is so much greater now. But time is cheap and disk space is not. (Really it is pretty cheap these days)
So now I have started the project to migrate our product databases to SQL 2016 to take advantage of the TDE backup compression as well as some of the other “Shiny New Toys ” that I am sure others will be talking about on this #TSQL2SDay.
Enjoy
