May 7, 2019 at 6:35 pm
Hi there,
I was tasked with TDE encryption of our Clients databases. I set up all the needed keys and certificates and encrypted couple pre production databases.
But now I am facing a problem. Our Company is utilizing Dell LiteSpeed software for SQL Server as out Backup and Restore tool. Compression level of this tool goes up to 12 times and backups and restores are fairly quick.
I backed up the encrypted database using LiteSpeed and it took forever to finish and encryption level was somewhat 1.4 times.
The same deal was with the restoring process. Our databases are big and restoring them for a long time is not appropriate for our environments. There is also a space consideration when compression level is even less than 2.
I tried using builtin SQL backup, Redgate backup; the same story.
Any recommendations for which tool can be used or maybe which method would be the prefered one?
You help with this issue is greatly appreciated.
Thank you.
Alex
May 7, 2019 at 6:38 pm
While I don't know the answer to your question specifically.. a word of caution.. if you're not on SQL Server 2016 CU7 or SP1 CU4 .. compression + encryption can cause corruption of your backup files.
May 7, 2019 at 7:06 pm
What SP, CU are you on with SQL Server 2016? And are you specifying maxtransfersize? There were some issues with TDE and backup compression, the gist of which are explained in this post:
Sue
May 7, 2019 at 7:59 pm
https://security.stackexchange.com/questions/19969/encryption-and-compression-of-data
May 7, 2019 at 8:16 pm
TL;DR
Because of the way modern encryption works ,very few strings are identical therefor:
Compression no happen good
May 7, 2019 at 8:30 pm
Alex -
With TDE, backup compression only kicks in if maxtransfersize is specified in the backup command and maxtransfersize must be greater than 65536 (64 KB). You'd need to check the current documentation for LiteSpeed. They used to recommend just setting compression to Level 0 or 1 with TDE databases but that was awhile back and with things changing with TDE and backup compression, I don't know what their latest recommendations are in regards to this. They used to have a pretty nice little write up about it.
Sue
May 7, 2019 at 8:47 pm
Thanks everybody for the valid responses, I really appreciate them.
Sue, LiteSpeed now supports TDE compression on SQL Server 2016 SP2 and higher. But I stuck with AlwaysOn on SQL Server 2012 Enterprise SP4.
Any other recommendations about any tools or any thoughts?
May 7, 2019 at 8:53 pm
If you're compressing 2012 backups I hope that you have a process in place to do test restores to make sure that they are valid. Otherwise I feel like you are playing with fire.
May 7, 2019 at 9:01 pm
Good point oogibah,
Before planning on implementing TDE, we did a lot of restores during pre-prod, testing, qa and other upgrades and never had problems.
With testing TDE encrypted compressed backup file restores we did not have problems as well.
The problem is time and size, that is not appropriate considering the fact that we used to have small and quick backups and restores before.
That's why we are trying to find a better tool.
May 8, 2019 at 7:04 am
So if you have set maxtransfersize equal or greater than 64KB and do understand that with encryption in general less equal sequences of data may exist (which will be those parts that actually make compression happen) and are still unhappy with the results, you should turn off TDE again.
I mean if you've tested backups and restore before you have noticed the larger backup sizes and it was okay but it's not anymore once it's live, something else went fundamentally wrong there.
You might encrypt your backups externally after writing it to disk, could see some improvement there in disk size but your DR Scenario would mean to remove encryption from the backup outside of SQL Server.
Just to make sure you understand: You will not be able to achieve close to same compression ratio with TDE enabled unless you're on SQL 2016 SP2 or higher.
May 8, 2019 at 7:42 pm
Thanks again for all responses,
But let me try to clarify something.
According to California Consumer Privacy Act (CCPA) of 2018, that will be in effect on 1/1/2020 the bill "would provide a private right of action in connection with certain unauthorized access and exfiltration, theft, or disclosure of a consumer’s nonencrypted personal information". I am guessing that every Company, that has any business in California, should be affected by this Act.
That is the reason why our client asked us to look into the TDE encryption of their SQL Server databases.
Currently production databases are not encrypted. We are utilizing Quest LiteSpeed for taking database backups and restores and never had problems with this tool before.
So in order to comply with the Client's request we are at Discovery stage now.
We just encrypted few clustered and not clustered SQL Server 2012 databases in our Lab environment.
You are probably aware that SQL Server 2016 Enterprise license is not cheap, and our Client is not planning on upgrading SQL Server 2012 Enterprise to 2016 until the next year. That's why we are where we are.
I hope I made myself little more clear this time. I was wondering, if anybody faced the problem with compression of TDE encrypted databases on 2012 platform before and have a good solution for that issue.
Thank you.
May 8, 2019 at 8:38 pm
You shouldn't need to justify what you're doing - it's something plenty of people have dealt with. Ignore some of that. But you are in a tough spot. I've had to look at that issue before as well.
Similar to 2012, On 2008R2 it was unfortunately a choice between backup compression or TDE. So to utilize TDE we had to look at ways to increase the performance of the backups. You can find a lot of articles on different things to tweak but the biggest gain I saw was with using stripped backups. After the backup, you could look into some other compression tool to address the space issue.
Here are a couple of links on speeding up the backups to get you started if you want to try testing some options:
MS SQL Server backup optimization
Is your SQL Server backup running slow? Here’s how you can speed it up
How to Make SQL Server Backups Go Faster
Sue
May 8, 2019 at 8:57 pm
I did in fact run into this issue, but our solution might not work for you
We have a datastore we move all but the most recent backup-set(we copy that.. so we have redundant copies) to in order to keep the servers from needing so much extra space.. and that is being deduped by windows... which makes it LOOK like its taking up a lot less space but its really just taking up space in the windows dedup chunkstore... It does dedup at 59% so it does save a decent chunk.
May 8, 2019 at 9:06 pm
Thanks for your all prompt responses.
I will check all these options and let you know about the output
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply