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 :)
The files are stored in a blob in the sharepoint database and as TDE encrypts all the pages in the database all the files will be encrypted there within.
It's important to notice that while the database is mounted on the server the database server will serve the files unencrypted to the Sharepoint application server and it's clients.
The Sharepoint binary cache will store them unencrypted as well as all the clients. You are only encrypting the data at rest on the SQL Server when using TDE.
You can add to the security by using encrypted connections to the database server and HTTPS to connect to the Sharepoint application but after the files leave the database storage they will be unencrypted.
Best Answer
Yes. TDE is implemented only at the storage layer. Data is decrypted as it is read from disk. Accordingly, nothing beyond the storage layer is affected.
I would highly recommend getting a full backup prior to encrypting just to be safe. However, it is highly unlikely that a problem will occur that will corrupt the database or leave it unusable.
As mentioned previously, the encryption/decryption is done at the storage layer, and the system uses the .mdf and .ldf files in the same way that it uses them for unencrypted databases. It simply encrypts data before writing, and decrypts data after reading.
The system will use significant CPU and disk I/O as it has to read, encrypt, and write every data page in the database.
No, the size of the database will stay the same. If you're using compression or other technologies it will continue to work with it at TDE is done only when reading and writing pages to disk.
The backups may be somewhat slower depending on a varying number of factors and the backup files will be somewhat larger if you were previously using compressed backups. How much will depend on the type of data and version of SQL Server (latest versions support compression on TDE databases). Yes, the data in the .bak and .trn files will be encrypted.
I would recommend concentrating on a nice cup of tea or coffee. While you are enjoying the beverage of your choice, keep an eye on the SQL Server error log. An outage is not required, but due to the resource utilization required to encrypt the database, having a maintenance window is ideal.
In general, the answer is "no." However, if a defect is found that affects only TDE, then it may be advisable to install the CU that includes the fix. We can't see into the future, but you will install the same CU and service packs whether TDE is used or not. It is simply a feature of the database engine, so all updates to the database engine will include all of the TDE functionality.