SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transparent Data Encryption (TDE) SQL Server 2008


Transparent Data Encryption (TDE) SQL Server 2008

Author
Message
Roy Ernest
Roy Ernest
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17366 Visits: 6910
Yes, I did.... I had trouble setting up the hyperlink for that and I have to Thank Steve for setting it up properly. And he did a great job editing. To be honest, I would never want to take his job of editing articles....heheheheh.
He is great at that. :-)

-Roy
Andrew Peterson
Andrew Peterson
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2734 Visits: 755
Great article. Looks like for now, TDE is a one way process. Select wisely!

The more you are prepared, the less you need it.
beezell
beezell
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2462 Visits: 499
Good article. Nice and concise, with good points. BOL pointed this out in regards to the read-only file groups:

While TDE operations are not allowed if the database has any read-only filegroups, TDE can be used with read-only filegroups. To enable TDE on a database that has read-only filegroups, the filegroups must first be set to allow writes. After the encryption scan completes, the filegroup can be set back to read only. Key changes or decryption must be performed the same way.

So there is a work-around, but it had better be known before doing encryption. Thanks again for the information!

Cheers,
Brian
Roy Ernest
Roy Ernest
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17366 Visits: 6910
Thanks for the Info Brian. That is something I missed.
Thanks to all who have commented and read.

-Roy
Matt Penner
Matt Penner
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 114
Thanks for the great article. I didn't see anything about impact on performance. Encrypting the entire database at the I/O level surly has some impact. You tested this on a 12GB database. Any chance you were able to run some scripts and get a notion of how responsive your database was after this? Is there anywhere I could find some initial statistics on how this impacted performance?

Thanks!
Matt Penner
Roy Ernest
Roy Ernest
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17366 Visits: 6910
Hey matt,

Last week there was an article written on the performance impact on TDE. In that the Author did some testing and was able to figure out that the performance impact was less than 5%.

-Roy
WayneS
WayneS
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42273 Visits: 10794
Hey Roy... very good (okay, GREAT) article.

I thing to emphasize... as long as you need that backup, you need to keep the security certificates. Think SOX. You may need that certificate for many years. And, of course, it can't be kept with the backup... sorta nullifies the security. How to manage the security of the certificates separately from the backups needs to be thought out in advance also.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Roy Ernest
Roy Ernest
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17366 Visits: 6910
I totally agree on that point. It should be stored in multiple medias I would say and kept in a very safe place off the network.

-Roy
Steven Harris-463201
Steven Harris-463201
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 73
Could you explain the difference between making the master key and the certificate. Also, I noticed that the master key password was set to an empty string. Why?

Thanks,
Steve
JJ B
JJ B
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1979 Visits: 2864
Excellent article. I like how you took the time to look up known problems with TDE and to write out a list of issues to take into consideration. In other words, this article is much more than a re-hash of BOL/here's how you do it. It gives great info. Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search