Upgrade Advisor (SQL Server Upgrade Assistant)

Transparent Data Encryption (TDE) is a SQL Server feature designed to protect data at rest by encrypting the physical
files of the database, including both the data and log files. Introduced in SQL Server 2008 Enterprise edition and now supported in Standard edition (from SQL Server 2019 onward), TDE helps organizations meet compliance requirements like GDPR, HIPAA, and PCI DSS.

How it works:

• TDE encrypts the data files using a Database Encryption Key (DEK), which is stored in the database boot record for quick
  access.
• The DEK itself is protected by a certificate stored in the master database, which is backed by the SQL Server Service
  Master Key (SMK).
• When the SQL Server instance starts, it can access the key hierarchy to decrypt and serve data as needed, making the
  encryption process seamless to applications and users.

Benefits of TDE:

• No application changes: Encryption and decryption occur transparently at the I/O level.
• Protects backups: Since encryption happens at the file level, backups are encrypted too.
• Minimal performance impact: Typically only 3–5% CPU overhead for most workloads.
• Built-in compliance: Helps demonstrate adherence to industry security standards.

Important considerations:

• You must back up and securely store the certificate and private key; without them, restoring the database is impossible.
• TDE does not encrypt data in memory or over the network—those require TLS and Always Encrypted, respectively.
• All tempdb activity is encrypted when any database using TDE exists on the server.

TDE is commonly used in regulated industries like healthcare, finance, and government to ensure that even if database files or backups are stolen, the data remains unreadable without proper keys. For SQL Server DBAs, TDE is a straightforward, robust way to bolster data security with minimal disruption.