SQL Server › Transparent Data Encryption

What is the hierarchy of encryption keys and certificates involved in setting up Transparent Data Encryption?

Intermediate

Answer

TDE relies on a hierarchy starting with a service master key at the server level, which protects a database master key, which in turn protects a server certificate, and finally that certificate is used to encrypt the database encryption key specific to each individual database that has TDE enabled, creating a chain of protection from the top level server security down to the actual data.
-- Setting up the key hierarchy from the top down
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'TDE Certificate';
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
Real-world example A database administrator carefully follows the correct order for setting up the encryption key hierarchy, starting with the master key, then the certificate, before finally creating the database encryption key needed to enable TDE.

Common follow-ups: What happens if the server certificate used for TDE is lost without a proper backup?;How often should the certificate used for TDE be backed up?