SQL Server TDE – Decryption and Encryption in Buffer Pool

OK, In case anyone wants to know, I got to the bottom of this.

The chain is pretty easy to work out:

Create Service Master Key (automatically done upon install)

Create Database Master Key (user creates their own key)

SymetricEncrypt(Service Key,Database Master Key) -> Store Encrypted Master Key into Master DB

Create Certificate

SymetricEncrypt(Database Master Key, Certificate Private Key) -> Store Encrypted private key in Master DB

Create Database Encryption Key (system creates a random key, but must be manually added to database)

ASymetricEncrypt(Certificate, Database Encryption Key) -> Store Encrypted DEK into database.

So, to decrypt the database data, you'll need to:

Use the SMK to decrypt the DMK

Use the DMK to decrypt the cert private key

Use the certificate to decrypt the DEK

Use the DEK to decrypt the database

The starting point is the Service Master Key... how is this stored (if you encrypt it, you can't retrieve data, and if left in plaintext your system is exposed).

The answer is, the SMK is encrypted with Data Protection API, which is a service built into windows.

Basically, data is encrypted with "secrets" from your user profile. So the same user profile must be used to decrypt the SMK.

Now that I know this, I can use DPAPI in my applications to encrypt my encryption keys :)

For specific part of Question you asked to Sean.

'thus, when the page is "read" by another transaction, the updated values are there.': How does SQL Server know that data on the disk is out of date and therefore should read (upon a select query) from the memory?

Suppose there is other request which comes and asks to read same page. SQL Server Buffer Manager will check the data cache in the buffer pool to see if it already has the page cached in memory. If the page is already cached, then the results are passed back to the Access Methods(Access Methods contains set of code which handles I/O requests for rows, indexes, pages, allocations and row versions) to continue with operation as requested. If the page isn’t already in cache, then the Buffer Manager will get the page from the database on disk, put it in the data cache, and pass the results to the Access Methods.

Is there an information on the page header that the data in the page on the disk is stale? Isn't there a performance penalty for this as it reads from the disk first and goes to memory next.

Yes there is. The header of each page in cache stores details about the last two times it was accessed, and a periodic scan through the cache examines these values. A counter is maintained that is decremented if the page hasn’t been accessed for a while; and when SQL Server needs to free up some cache, the pages with the lowest counter are flushed first. The process of “aging out” pages from cache and maintaining an available amount of free cache pages for subsequent use can be done by any worker thread after scheduling its own I/O or by the lazywriter process.

Having said that I would like you to read How It Works: Bob Dorr's SQL Server I/O Presentation

Take the following as an example update tblxxx set col1 = GetDate() where the table is 1 billion rows.

Simplified the process to discuss.

1. Begin transaction--Transaction starts here

2. Fetch Page--Page is fetched from Disk to memory

3. Acquire Lock

4. Acquire Latch

5. Generate log record--Change to the page generates Log records

6. Update page LSN to match that of the log record--When change to a page generates Log records They have LSN numbers identifying them and the same LSN number is stamped on header of the page change to which generated these log records.

7. Change Data (dirty the page)--Since page changes as it was on disk. It is now dirty

8. Release Latch

9. Commit Transaction Starting--Commit issued for transaction

10. FlushToLSN of Commit--LSN corresponding to commit is flushed to log disk and recorded. This is done before dirty data page is flushed to disk

11. Release locks

12. Commit Transaction Complete

The entire table won’t fit into memory so lazy writer will write dirty pages to disk. In doing so lazy writer will first call the routine FlushToLSN to flush all log records up to and including the LSN stored in the page header of the dirty page. Then lazy writer, and only then, will it issue the write of the data page. If a power outage occurs at this point the log records can be used to reconstruct the page (rollback the changes).

Notice that the latch only protects the physical access to the in-memory data page only for the small about of time the physical change is being made. The lock protects the associated data until the commit or rollback takes place.

This allows lazy writer to remove a dirty page while the locking structures continue to maintain the transactional integrity.

The question which can also arise is if rollback is issued instead of a commit would SQL Server fetch back in pages to undo the update? The answer is yes. The page could have been removed from buffer pool/page cache by lazy writer after the FlushToLSN took place. When the rollback occurs the pages changed by the transaction may need to be fetched into buffer pool to undo the change.

PS: Feel free to ask if any further clarification is required.