Transparent Data Encryption (TDE) in SQL Server, Explained

What TDE Actually Protects Against

TDE encrypts the physical data and log files on disk. The threat model it addresses is narrow and specific: someone who steals the physical disk, a backup file, or a database snapshot — without also having the encryption key — gets unreadable bytes instead of your customer data.

Scenario	Does TDE protect against it?
Stolen physical disk or storage snapshot	Yes — this is exactly what it's for
Stolen `.bak` backup file	Yes — an encrypted database's backups are encrypted too
A logged-in application user querying the table	No — TDE is transparent to any authenticated query
A DBA with `sysadmin` reading the data directly	No — TDE does not restrict access, only at-rest exposure
Data intercepted on the network between client and server	No — that's what TLS/encrypted connections are for
A single sensitive column that needs to stay opaque even to DBAs	No — that's what Always Encrypted or column-level encryption is for

⚠ TDE is not "database encryption" in the broad sense the term implies to non-technical stakeholders. Enabling TDE and telling an auditor "the database is encrypted" without qualifying what threat it covers is how compliance gaps survive an audit until an actual incident. Pair it explicitly with TLS in transit and, where genuinely warranted, column-level encryption for individual fields.

The Key Hierarchy

TDE's encryption chain has four links, each protecting the one below it. Understanding this hierarchy is the difference between a routine key rotation and an unrecoverable database.

Service Master Key (SMK)
    └── protects → Database Master Key (DMK, in the master database)
            └── protects → Certificate (in the master database)
                    └── protects → Database Encryption Key (DEK, in the target database)
                            └── encrypts → the actual data and log files

-- The setup sequence, in order
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'A very strong password, stored in a vault';
CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE Certificate for ProductionDB';

USE ProductionDB;
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

ALTER DATABASE ProductionDB SET ENCRYPTION ON;

The Backup You Cannot Afford to Skip

The certificate protecting the database encryption key lives in master, not in the encrypted database itself. If you restore an encrypted database to a different server — for disaster recovery, or simply moving to new hardware — without that certificate and its private key, the database is permanently unreadable. There is no recovery path. Not from Microsoft, not from anyone.

⚠ Back up the certificate immediately after creating it, before you enable encryption, and store that backup somewhere entirely separate from the database backups it protects — a lost certificate with intact database backups is exactly as unrecoverable as no backup at all.

BACKUP CERTIFICATE TDE_Cert
    TO FILE = 'D:\Secure\TDE_Cert.cer'
    WITH PRIVATE KEY (
        FILE = 'D:\Secure\TDE_Cert.pvk',
        ENCRYPTION BY PASSWORD = 'A different very strong password'
    );

-- Store both files, and the password, somewhere the database backups are NOT —
-- a separate vault, a separate access-control boundary, ideally a separate physical location.

Restoring onto a new server requires recreating the master key and restoring the certificate before the encrypted backup will restore successfully:

-- On the destination server, before restoring the encrypted database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master key password on this server';

CREATE CERTIFICATE TDE_Cert
    FROM FILE = 'D:\Secure\TDE_Cert.cer'
    WITH PRIVATE KEY (
        FILE = 'D:\Secure\TDE_Cert.pvk',
        DECRYPTION BY PASSWORD = 'The password used when backing up the private key'
    );

-- Now the RESTORE DATABASE command will succeed

Performance Impact

TDE encrypts and decrypts at the page level as pages move between disk and the buffer pool — pages already in memory are not re-encrypted on every read, so the steady-state overhead is modest on modern hardware (commonly cited in the low single-digit percent range for CPU, though this varies by workload). The place you'll actually notice it is during the initial encryption scan of an existing database, which touches every page once and can take a meaningful amount of time on a very large database — plan that as a maintenance-window activity, not something to flip on mid-day against a large production table.

-- Monitor the initial encryption scan's progress
SELECT
    DB_NAME(database_id) AS database_name,
    encryption_state,        -- 3 = encrypted, 2 = encryption in progress
    percent_complete,
    encryptor_type
FROM sys.dm_database_encryption_keys;

Certificate Rotation

Certificates should be rotated periodically as a matter of security hygiene, not just when compromise is suspected. Rotating the certificate does not require re-encrypting the data — only the (much smaller) database encryption key gets re-wrapped under the new certificate:

USE master;
CREATE CERTIFICATE TDE_Cert_2026 WITH SUBJECT = 'TDE Certificate 2026 rotation';
BACKUP CERTIFICATE TDE_Cert_2026 TO FILE = 'D:\Secure\TDE_Cert_2026.cer'
    WITH PRIVATE KEY (FILE = 'D:\Secure\TDE_Cert_2026.pvk', ENCRYPTION BY PASSWORD = 'New password');

USE ProductionDB;
ALTER DATABASE ENCRYPTION KEY
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_2026;

-- Keep the OLD certificate backup too — any backup taken before the rotation
-- still needs the OLD certificate to restore

Keep every historical certificate, not just the current one. A backup taken under last year's certificate needs last year's certificate to restore, regardless of how many times you've rotated since. Treat the certificate archive with the same retention discipline as the backups themselves.

A Practical Checklist

Back up the certificate and private key before turning encryption on, to a location separate from database backups.
Store the certificate backup password in the same secrets vault as other production credentials — not in a script, not in a ticket.
Schedule the initial encryption scan for a maintenance window on large databases.
Rotate the certificate on a defined cycle, and retain every prior certificate for as long as backups taken under it remain in your retention policy.
Document TDE explicitly as "encryption at rest" in any compliance evidence — pair it with TLS-in-transit and column-level encryption where the data classification calls for it, rather than letting TDE stand in as the whole answer.
Test a full restore onto a clean server, including certificate restore, at least once — the first time you do this should not be during an actual disaster recovery.

The Bottom Line

TDE is straightforward to enable and genuinely effective for its specific job: making a stolen disk or backup file worthless without the key. Its entire operational risk lives in the certificate — lose it, and an intact backup becomes permanently unreadable. Back it up immediately, store it separately from the data it protects, and rehearse the restore before you need it for real.