I'm in the process of researching TDE implementation on a VLDB and would like to know if anyone has real world experience with enabling encryption on a production server.
What kind of problems (if any) can I expect? My testing shows very little impact in a test environment, but I'm concerned about how long this is going to take to setup in prod. Any feedback is appreciated.
I read somewhere that I'll need to disable Backups and database maintenance during the encryption process. Is this true?
This is very concerning, since my testing took ~4 days to complete the encryption.
Best Answer
We have recently implemented TDE along with AlwaysON in Production running SQL Server 2014.
In our application, we noticed a slight (1-3%) increase in CPU utilization. Your environment is different, so do a thorough load testing with some realistic PROD datasize.
Takeaways from embracing TDE :
Backup compression and TDE does not go hand in hand i.e. You wont be able to leverage backup compression when you enable TDE. This means your VLDB backups will be HUGE !
Instant File Initialization is not available when you enable TDE.
When restoring the databases from PROD to other environments (e.g. UAT, etc), you need to export the certificate so that the database can be restored.
You need to properly keep safe and backup certificates that you use to enable TDE.
Enabling TDE on a VLDB takes time. You can use this script to list out the databases, encryption status along with other useful information.
Remember: TDE will encrypt data at rest (on disk) ONLY. You still have to use encryption (e.g. force encryption) to protect data inflight.
Edit:
SQL server 2016 now supports backup compression on TDE enabled databases