For years we have been able to use Transparent Data Encryption or TDE to encrypt our database files on SQL Server Enterprise Edition. This ability was the only reason many clients needed to upgrade from Standard Edition to Enterprise Edition.
SQL Server 2019 includes TDE as a Standard Edition feature. No longer do you need to shell out the extra dough to upgrade to Enterprise Edition just for TDE.
What is TDE?
Transparent Data Encryption encrypts your SQL Server data and log files on disk. Some call this encryption at rest. What this also does is encrypt your backups, since your backups are just copies of the data pages.
TDE Gotchas? Things you need to be aware of:
- You must backup your encryption keys if you are going to restore your backups. This can easily be overlooked. I recommend you test restring your backups to a different server than where they were backed up to. This will ensure you have all the right keys in place.
- If you are using compressed backups, which may give you something like 60% savings on your backup file size, this will change with TDE. With TDE, the compression is compressing encrypted data pages instead of plain text type data the SQL Server compression does not do such a good job. If you turn compression on with TDE encrypted databases you would be lucky to get a 5% savings. I generally don’t recommend using compression with TDE backups since the savings isn’t substantial.
- If you TDE encrypt a log shipping database that will break the log shipping and you will need to restart the log shipping process.
We recommend protecting your data with TDE. There is no extra cost for this if you are on SQL 2019 Standard Edition or newer.
Need help configuring TDE?
Stedman Solutions can help.
Just contact us
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!