What's it all about?
If you or your company use Microsoft SQL Server, you should probably know the following about TDE.
In case you don't know, TDE stands for Transparent Data Encryption.
For many users of the Enterprise Edition of SQL Server, TDE was the killer feature that made the Enterprise Edition the must-have edition.
But all that has changed.
With every new version of SQL Server, Microsoft adds a couple of features into Standard Edition feature set.
The most striking change in the feature set of Standard Edition SQL Server 2019 is by far Transparent Data Encryption (TDE) support.
Why is TDE a killer feature?
Transparent Data Encryption is the capability to have all your data stored encrypted on disk. This is also known as encryption at rest. This covers data files, log files and backups.
TDE provides this without you having to change anything in your applications or code (hence the transparent part).
Given the upsurge in awareness of data protection – largely driven by General Data Protection Regulation (more commonly known as GDPR) and other data regulation – encryption is a hot topic.
You either know or will probably discover that management in most organisations tend to be keen to have encryption at rest. Especially if it doesn’t cost too much and is easy to implement. It’s a very desirable feature.
That makes the arrival of TDE in SQL 2019 Standard Edition big news.
It means you no longer have to pay Enterprise license fees to secure your data at rest.
You would think that Microsoft would be shouting from the roof tops about the availability of TDE in SQL 2019 Standard Edition. But, no, barely a whisper.
How does TDE work?
TDE is an encryption mechanism that secures data at rest which means it encrypts data on physical disk.
Because you don’t have to install a new driver or change your code base, the data is really transparent to you.
Because it is a physical security it means your data is still vulnerable in buffer cache, network traffic and so on.
Nevertheless, if your data centre is burgled, thieves will be disappointed when they realize your data is encrypted on disk. Encryption of the database file is performed at the page level. Pages are encrypted first then written to the disk; they are decrypted when read into memory.
TDE secures data in:

  • Database backups
  • Database data files
  • Database snapshots
  • Database transactional log files
  • TempDB

TDE doesn’t deal with:

  • Buffer Pool Extension (BPE)
  • SQL Dump Files

TempDB may be a surprise for you. Yes, TempDB will be encrypted if any database is encrypted with TDE.
For more information on how TDE works, please see: https://docs.microsoft.com/en-gb/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15#transparent-data-encryption-and-the-tempdb-system-database
Of course the Enterprise Edition has numerous other advantages such as Always On Availability Groups (Standard only has Basic Availability Group), Online Page Restore, Online Index Create and Rebuild to name a few.
We recommend protecting your data with TDE.
If you already have an Enterprise Edition of SQL Server or the SQL 2019 Standard Edition and are not using TDE, I strongly suggest you look at employing TDE.
If you don't have either an Enterprise Edition of SQL or the SQL 2019 Standard Edition, I strongly suggest you get the SQL 2019 Standard Edition and make use of TDE.
And, if nothing else, knowing that this killer feature is now available on the SQL 2019 Standard Edition is something you can impress your friends and colleagues with.
I hope you find this information helpful. We will be happy to answer any questions you may have.