How to enable TDE?

TDE is "Transparent Data Encryption", TDE provide encryption and decryption input and output files with data. TDE uses "Database Encryption Key" saved in Master database. To turn on TDE must perform three conditions:

1) We have to create master key in master database

2) We have to create certificate in master database

3) We have to create database encryption key in user database

All these conditions will do via T-SQL:

State of encryption database you can find in system view:

- sys.dm_database_encryption_keys

Interesting facts about TDE:

- When mirroring databases are encrypted both databases

- When encryption is enabled on the database to encrypt all full-text indexes

- If database contains Read-Only filegroup, encryption failure

- When encryption is enabled you can't use backup with compression (Expect MSSQL 2016)

See you next time!