TDE pros cons and best practices.

Question

hi folks,
we are planning to implement TDE on one of our databases. Can someone tell me any performance issues or any other database hit that can occur after implementing this.
Also what are the best practices (must do's) to maintain inventory of TDE configurations like passwords/certificates backups?

My concern is, if TDE is implemented and in a scenario where whole server crashes and i have to reinstall SQL on brand new server what passwords/certificates should i must have on hand to restore the database from the backup.

Answer 1

Hi @Heisenberg ,

Welcome to Microsoft Q&A!
It’s important to back up the certificate and keep this safe, as this will be needed if you want to restore the database onto another server. New server will need to have a master key set up in order to encrypt the certificate. Thus, if you are restoring the encrypted database to another instance, you will need the certificate and its private key as well as the backup file.

Advantages of TDE

• Fairly simple to implement.
• No changes to the application tier required.
• Is invisible to the user.
• Works with high availability features, such as mirroring, AlwaysOn and log shipping.
• Works with older versions of SQL Server, back to 2008.
  Disadvantages of TDE
• Only encrypts data at rest, so data in motion or held within an application is not encrypted.
• All data in the database is encrypted – not just the sensitive data.
• Requires the more expensive Enterprise Edition (or Developer or DataCenter Edition) of SQL Server.
• The amount of compression achieved with compressed backups will be significantly reduced.
• There is a small performance impact.
• FileStream data is not encrypted.
• Some DBA tasks require extra complexity, for instance restoring a backup onto another server.
• As TempDB is encrypted, there is potentially an impact on non-encrypted databases on the same server.
• The master database, which contains various metadata, user data and server level information is not encrypted.

Recommendations and Best Practice

• If your database doesn’t need encryption then don’t implement TDE on it – may affect performance for non encrypted databases on the same instance due to addition of the encrypted TempDB.
• Backups – always backup your databases before encrypting them, just in case.
• Storage of encryption keys – make sure these are stored safely, as these will be needed to remove encryption. If disaster occurs and you need to restore the database to another server from a backup file then the backup will be useless without the certificate and private key.
• Extended backup duration – encrypted backups don’t compress well, so expect backups to be larger, and take longer to run.
• TDE isn’t an end to end encryption solution - don’t expect data to be encrypted in transit, or within the application even if you have TDE enabled. TDE encrypts the data (e.g. .mdf and .ldf files) and backup files (e.g. .bak), nothing more.
• Implement other data access controls - TDE complements, but does not replace, other methods of securing data, so access control (via permissions), password encryption and securing network traffic are still important.

I found these articles with a complete description. I hope they help you.
Pros and Cons of Transparent Data Encryption (TDE) Part 1 of 3
Setting up Transparent Data Encryption (TDE) Part 2 of 3
Best Practices for Transparent Data Encryption (TDE) Part 3 of 3
Transparent data encryption (TDE)--official document

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Nice explanation. Great job. Please keep post.