In the recent past, I have been traveling to Delhi to meet customers and also attend a few conferences which my company has been sponsoring. These travel experiences give me an opportunity to meet folks who read my blog regularly as a face-to-face interaction. Most of these interactions mean I get to spend time with really smart people who quiz me for which I get back to my blog for answers. Last week, when I was at a conference, one DBA walked up to me and said – “Hey Pinal, I am a DBA from a reputed banking company. We are in the process of deploying TDE for one of our databases which is in SQL Server 2008 R2. I had a few doubts, can I ask you?” Well, as simple as this interaction went, the whole conversation spanned for close to 30 mins and I am doing a summary of the conversation in this blog for your reference.
When does TDE encrypt the DB?
Once Transparent Data Encryption is enabled by issuing the “Alter Database” command, SQL Server performs basic checks such as Edition Check, Read-only Filegroups, and presence of DEK etc. Once the checks are complete, the command returns immediately with success. This does not mean that the database encryption is complete. Since Encryption is done in the I/O path, all the data pages that are already written to the disk prior to enabling the Encryption have to be read into the memory and then written back to the disk after encrypting the page. This process is also referred to as “Encryption scan”. This task is carried out by Background processes (system SPIDS). The encryption scan, which runs asynchronously to the DDL, takes a shared lock on the database. All normal operations that do not conflict with these locks can proceed without being blocked.
You mentioned READONLY DB, tell me more?
Transparent data Encryption does not work on a database that contains any filegroups that are marked Read-Only or any files that are marked as Read-only. Alter Database fails with an error message (33118) clearly indicating the reason for the failure. Users can enable read-only property on the filegroups once the encryption scan is completed. However, no DDL related to TDE (DEK change) can be run on the database until the read-only property is removed.
What happens to TLog files?
Encryption works differently on a Transaction Log and is complicated. Since 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. Similarly, the log header cannot be re-written because of this write-once principle so there is no guarantee that log records written to the log even after TDE is enabled will be encrypted. The smallest unit of encryption for log files is a virtual log file (VLF). So either an entire virtual log file (VLF) is encrypted or it’s not. Also the entire VLF 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, this new VLF will be encrypted. So there is no deterministic order between when data pages are encrypted by the scan vs. when the log is encrypted.
I thought these were some of the interesting conversations I have had in the recent past that are worth a mention to share. If you have used TDE in your environment, do let me know. I would love to know your experiences in working with it. Is there any catch that you want me to explain next time? Write it over as comments and would love to explore more.
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
