Transparent Data Encryption (TDE) in SQL Server

Ben Miller deserves credit for this post.  I have picked up a little bit of information about TDE here and there but he pulled it all together for me.  I presented this at work to give a broad overview of what it is and why we would use it.  I will be creating a more in depth post later.

What is it?

  • Encrypted Data at rest.
  • AES (128,192,256) or 3DES
  • Encryption is performed at the page level.
  • Datafile, Logfile and Tempdb are encrypted.
  • Tempdb is encrypted at AES 256 and you can’t change that.
  • FileStream data is not encrypted when TDE is enabled.
  • Protects against people stealing your files.
  • SELECT statement results are not encrypted so it is Transparent to the user.

Benefits

  • No Schema changes like cell level encryption.
  • Page level encryption
  • MSFT estimates degradation at 3 to 5% instead of 20 – 28% that occurs with cell level.
  • Secure backups by default.
  • Invisible to user

Disadvantages

  • Backup compression is not useful when TDE is enabled.
  • Enterprise Edition Only
  • With Cell level encryption, you have finer control over encrypted elements
  • Tempdb is encrypted even if only one database is encrypted.
  • Instant File initialization is not available when TDE is enabled.
  • If you lose your Certificate, your data is gone.

Happy Encrypting!

Andrea

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

One thought on “Transparent Data Encryption (TDE) in SQL Server

  1. andreaallred says:

    Thank you! I am always happy to hear when something helps out. Congrats on lunch too! That makes it even better. Thank you for taking the time to leave such kind words.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s