Transparent Data Encryption is introduced in SQL Server 2008. This feature has been introduced to provide more security to the data stored at the database level. It has the most valuable features which will help secure data by help of exploiting multiple CPUs and less increase in the disk space used to save encrypted data.
Why Transparent Data Encryption?
- Companies which maintain sensitive data should comply to more stringent security standards.
- Data Encryption should be transparent to the applications using the database.
- Encryption should be extended to the data at rest.
- TDE helps with real time I/O encryption of data and log files.
How Transparent Data Encryption Works
TDE is enabled by using ALTER DATABASE command. After running the Alter SQL Server performs basic checks such as Edition Check, Read-only Filegroups, and presence of DEK etc. It returns immediately with success message but the database is not completely encrypted. A background process with shared lock runs to encrypt the database. Encryption is done in I/O path – RE-ENCRYPTION SCAN/ ENCRYPTION SCAN. All the data is read into memory and written back after encrypting. SELECT * FROM sys.dm_exec_requests returns command type “ALTER DATABASE E” and status “background process”.
The encryption status is stored at regular intervals to recover in case of server restart. TDE related DDLs do not work when any of the Filegroups is in Read-Only mode. Data files are encrypted at page level. 32 pages encrypted in a single go and checkpoint for every 1024 pages is issued. The process sleeps for 250ms for every 32 pages.
TDE works on the concepts of second checksum. Page header is also encrypted and checksum is for the encrypted data in the page. It is calculated and saved in header before encryption. Second checksum is used to check the decrypted data.
TDE Encryption does not encrypt already written log records in log files. Granularity of encryption in log files is at VLF level. This means that once the TDE is implemented the next active VLF will be encrypted. An overhead of one log record for every 4 extents (32 pages) encrypted is added to the log file.
TEMPDB is encrypted when any database is encrypted. TEMPDB Encryption is always with AES_265 algorithm. Encryption once done on TEMPDB stays even after Encryption is disabled on all user databases. Only TEMPDB system DB can be encrypted. Encryption Scan cannot be rolled back. Background encryption process can be pause using Trace Flag 5004 and slowed down using Trace Flag 5005.
Implementation of TDE
Below is a demo to implement TDE.
1. Creating Database Master Key:
use master; go; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Myp@ssword123’; go;
2. Creating a Certificate:
use master; go; CREATE CERTIFICATE MyCertDEK WITH SUBJECT = 'My DEK Certificate’; go;
3. Backup the Certificate:
BACKUP CERTIFICATE MyCertDEK TO FILE = 'path_and_file_name.cer' WITH PRIVATE KEY ( FILE = 'C:\MyCertDEK_Bak.pvk' , ENCRYPTION BY PASSWORD = 'Myp@ssword456' ); Go;
4. Create a Database to implement TDE
Create Database MyDBForTDE GO;
5. Create Database Encryption Key in the User DB
use MyDBForTDE; go; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyCertDEK go;
6. Enable the DB for Encryption
ALTER DATABASE MyDBForTDE SET ENCRYPTION ON GO;
Impact of TDE
You have to note these important points when you are going to implement TDE.
- Backup/Restore and Attach/Detach
Certificate must be backup with DB backup
Target server should have database master key and Certificate used for encrypting DEK on source
Backup compression has little or no effect
Third party backup tools which use log scan will break
Database compression is effective as it reduces I/O
- Key Management
Certificate change is faster as it encrypts only DEK
ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE 
ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM = AES_128
- High Availability
No effect on Clustering and Replication
Mirroring and Log shipping –
- Create Database Master Key
- Backup and restore Certificate from Primary/Principal to Secondary/Mirror
- Recovery Process
Recovery Process is single threaded
Recovery time is longer
Impact on Tools
Utilities that scan Transaction Logs will fail
Inbuilt functions that use log and VDI are not affected
Issues with TDE
Below are few issues you may see when you implement TDE.
High CPU Utilization – Expect high CPU utilization when you are going to implement TDE. We are gaining the benefits of security by exploiting CPU. It is recommended to implement TDE during low or no business hours and only on servers with more CPU to exploit. When you see CPU spikes as soon as you implement TDE check encryption status by using the DMV, sys.dm_database_encryption_keys. You can also use trace flags 5004 (pause background encryption process), 5005 (Slow down background encryption process)
Slow Query Performance – Expect to see slow performance of query on unencrypted databases. Performance degradation of 2-3% under normal CPU utilization and ~30% under 100% CPU utilization is expected.
High Disk Space Utilization – Check if snapshots are running for the database. Large database encryption can increase log file size significantly as the encryption process of whole database is logged.
Backup Restore Issues – Database Master key already exists on target server. There may be a missing certificate. Certificate must be backup using WITH PRIVATE KEY option. Changed DEK must be created on destination which is changed on source between log records.
Cannot Encrypt Database – Check the Edition of SQL Server and any read only file groups. Check the DMV sys.dm_database_encryption_keys to see if encryption is already enabled.
Mirroring and Log Shipping – When the data is out of Sync, Check for certificates on Secondary/Mirror server. Expect significant overhead due to log record for encryption.