Transparent Data Encryption (TDE) encrypts the data within the physical files of the database. If you do not possess the original encryption certificate and master key, the data cannot be read when the drive is accessed or the physical media is stolen, this is what we call encryption for data at rest. Transparent Data Encryption (TDE) is was introduced in SQL 2008 and later refined in SQL 2012; when a database is encrypted, the data files, log files, and backup files are encrypted. Once TDE is enabled for a database, the SQL instance encrypts data before it’s written to disk and then decrypts the data when read from the disk. The best part of this feature is, as its name implies, it’s completely transparent to your application. This means no application code changes (only administrative changes to enable it for a database) are required and hence no impact on the application code\functionalities when enabling TDE on a database referenced by that application.
What's Great About TDE
• TDE is completely transparent to the application- no changes are required on your line of business application.
• TDE is enabled on the database data files and log files and subsequently backup files. This means that backups will need to be decrypted before they are moved/restored to another system.
• TDE encryption will make changes to the instance level in the namely temp DB Downsides To TDE
• Yes! while it sounds great to encrypt all of your data with no changes to your line of business application, you will have a performance penalty from 3% to 5% of your CPU.
• You will need copies of the SQL Server certificate and private key on all SQL instances where we want to move/restore data from production