by

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

by

SQL Heroes – Thank you for the inspiration

The plan is for this to be a technical blog, but before that can happen there are people that I need to thank for inspiring me to get this far.  There are so many people that I would love to include here, but I had to pick a select few.  To everyone else, *big hug* and thank you for being there for me.  I love how we really do become a big family as we help the community to grow.

Pat Wright – Thank you for pushing me to present at the SLC Users Group #slcsql.  His passion for SQL Server and helping others be better is evidenced in the amount of time he dedicates to our group.  He is always willing to give good and useful feedback and helps everyone to feel welcome at the group.  He also took the main picture on my blog and is an incredible photographer.  Thank you Pat for sharing your passion and helping me feel included in this great community.

Buck Woody – From the first time I walked into one of Buck Woody’s classes he has been one of my favorite people.  He is friendly and so funny.  I know that I probably threw his whole class off by having the “noisy blonde in the front” but he was still kind.  I am always thankful for the classes he posts and his constant commitment to teaching others.  Buck makes people feel like they are the most important person in the room which is a talent that I am striving to learn. Thank you Buck for always being so kind.

Brent Ozar – He may not remember this, but a few years ago I cornered Brent and asked about how to network and present and how to get my name out.  He was really helpful and spent time introducing me to people.  Because of his ability to talk to anyone, I pretended that I could talk to anyone at this most recent PASS Summit and I met so many people!  Brent is so much fun with costumes and that is one of my favorite things so I love that I can now merge my love of costumes with my love of SQL.  He is willing to answer my silly questions and help me better understand what I can do to be better.  Thank you Brent for your confidence and letting me borrow it this last summit.

Matt DeWitt – This list wouldn’t be complete with out my very first mentor. Matt believed in me when no one else thought I could be a DBA.  He would take time to point me in the right direction and help me correct the small and very large mistakes I made along the way.  He has been my emergency DBA contact for when things blew up and I didn’t know what to do.  His patience with me has been limitless and I am so thankful that he didn’t give up.  Thank you Matt for believing in me.

Now I come to you, kind reader.  Thank you for being here and please help me continue to improve.

Have a magical day!

Andrea