In the world of data security, Transparent Data Encryption (TDE) plays a crucial role in protecting sensitive information. TDE is a feature in SQL Server that allows you to encrypt your database at rest, providing an additional layer of security.

Recently, during one of my conferences, I had an interesting conversation with a DBA from a reputed banking company who was in the process of deploying TDE for one of their databases in SQL Server 2008 R2. In this blog post, I will summarize our conversation and provide you with a better understanding of TDE.

When does TDE encrypt the database?

Enabling TDE on a database involves issuing the “Alter Database” command. However, it’s important to note that the encryption process is not immediate. SQL Server performs basic checks such as Edition Check, Read-only Filegroups, and presence of DEK (Data Encryption Key). Once these checks are complete, the command returns with success, but the encryption process is still ongoing.

Since encryption is done in the I/O path, all the data pages that were written to the disk prior to enabling encryption need to be read into memory and then written back to the disk after encrypting the page. This process, known as “Encryption scan,” is carried out by background processes (system SPIDs). During the encryption scan, a shared lock is placed on the database, allowing normal operations that do not conflict with these locks to proceed without being blocked.

What about READONLY databases?

TDE does not work on a database that contains any filegroups marked as Read-Only or any files marked as Read-Only. If you attempt to enable TDE on such a database, the “Alter Database” command will fail with an error message (33118) indicating the reason for the failure.

However, once the encryption scan is completed, you can enable the read-only property on the filegroups. It’s important to note that no DDL related to TDE, such as DEK changes, can be performed on the database until the read-only property is removed.

What happens to Transaction Log files?

Encryption works differently on Transaction Log files. Since the Transaction Log is designed to be write-once fail-safe, TDE does not attempt to encrypt the contents of the log file that were written to the disk already. Additionally, the log header cannot be re-written due to this write-once principle, so there is no guarantee that log records written to the log after TDE is enabled will be encrypted.

The smallest unit of encryption for log files is a virtual log file (VLF). An entire VLF is either encrypted or not, and it is encrypted with the same key. When encryption is turned on for a database, the next time the log manager moves to a new VLF, that VLF will be encrypted. Therefore, there is no deterministic order between when data pages are encrypted by the scan and when the log is encrypted.

These were some of the interesting conversations I have had recently regarding TDE. If you have experience working with TDE in your environment, I would love to hear about it. Feel free to share your experiences and any questions you may have in the comments section below.

Stay tuned for more informative articles on SQL Server concepts and ideas!