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


TDE and Tempdb


TDE and Tempdb

Author
Message
Jayakumar Krishnan
Jayakumar Krishnan
Say Hey Kid
Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)Say Hey Kid (698 reputation)

Group: General Forum Members
Points: 698 Visits: 809
Comments posted to this topic are about the item TDE and Tempdb

Thanks
Jay
http://www.sqldbops.com
Kari Suresh
Kari Suresh
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 373

Good question.. Also the tempdb system database is a global resource that is available to all users connected to the instance of SQL Server



KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
tommyh
tommyh
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1788 Visits: 2000
The link leads to a page that says "This might have a performance effect for unencrypted databases on the same instance of SQL Server". No "great" word in that sentance. And i cant really find anything there that might indicate that encryption can be good for performance ("great" in my world is good). So anyone might wanna clearify this a bit? (since i dont have a clue)
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3736 Visits: 4408
This could have a great performace effect for the other unencrypted databases

I agree with tommyh, the word 'great' is misleading in this context.

Not being a native English speaker, I deciphered that sentence as 'Performance will greatly decrease if tempdb is encrypted'. After that I googled 'transparent data encryption performance' and found this article: http://www.databasejournal.com/features/mssql/article.php/3815501/Performance-Testing-SQL-2008s-Transparent-Data-Encryption.htm
Rob Garrison, the author of the article, emulated an OLTP environment and found that the average TDE overhead was 6.36%. Should this number be considered as 'great'? I don't think so :-)

The correct answer should be 'none of the above', because:
– 'the Tempdb also automatically gets encrypted on that instance' – true;
– 'this could have a great performace effect for the other unencrypted databases' – false.
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11186 Visits: 12004
I, too, chose the "none of the above" answer.

In addition to the links already posted by other contributors, here is a third one:
"Microsoft has estimated the entire impact of TDE on a SQL Server instance to be 3-5% depending on the server environment and data volume." (reference: http://www.simple-talk.com/sql/database-administration/transparent-data-encryption/)
In my opinion, 3-5% is far from "a great impact". The statement is partially true, partially false, and since there was no "both" or "it depends" option, I chose "none of the above" as the most correct choice.

A good but somewhat flawed question. If it can be changed to read "This could have some performance effect for other unencrypted databases", it would be a great question.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
DugyC
DugyC
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1430 Visits: 779
I also was caught out by the use of the word "great" which I interpreted as a "positive" performance impact.

Therefore considered the statement false as the impact is negative performance by whatever amount either significant or minimal if managed correctly.

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
chris.reynolds
chris.reynolds
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 77
DougieCow (6/29/2010)
I also was caught out by the use of the word "great" which I interpreted as a "positive" performance impact.


Me too. Great is, perhaps, too subjective a word.
mzettler
mzettler
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 133
I'm a bit confused with the answer to be honest. I saw the reference article and followed the link concerning the tempdb (http://msdn.microsoft.com/en-us/library/ms190768.aspx) where it explicitly said the encryption option was set to NO by default and could not be modified. Am I missing something here?
SQLBalls
SQLBalls
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 630
I agree with everyone else.

I went to the link on MSDN to read about this, and it says basically that it "might" have an impact.

I've read the article as well saying 3-5% based on system.

I've got about 20 systems using TDE, if there are some metrics or data saying this does effect databases and here are some examples I'd like to see it.



Depending on load, depending on server resources, depending on instance build, depending on TempDB being on a dedicated disk with multiple datafiles, there is a lot that needs to be quantified before this question can be made black and white.

Not to mention that TDE uses the Windows API to encrypt the files on disk. Pages in memory are NOT encrypted, the TempDB files in memory would feel no ill effect from this if they have enough memory to opperate in. this would effect a server during the checkpoint where all pages are flushed to the disk. Which once again depending on how the server is sized, the load in place, and the volume of users may make a little, a lot, or no difference whatsoever.

the wording is also confusing, but I dislike the question because it makes a statement and then gives you litteraly no info to back it up.

the TDE page on MSDN links to the Understanding the TempDB page on MSDN which has absoluetly nothing on it regarding TDE.

I get it that Microsoft put it on MSDN, but come on flush out a question before you make a statement.

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11186 Visits: 12004
mzettler (6/29/2010)
I'm a bit confused with the answer to be honest. I saw the reference article and followed the link concerning the tempdb (http://msdn.microsoft.com/en-us/library/ms190768.aspx) where it explicitly said the encryption option was set to NO by default and could not be modified. Am I missing something here?

You can not explicitly switch TDE on in the temdb database.
But you can switch TDE on in a user database, and that will result in SQL Server automatically starting to use TDE in the tempdb as well. This is required, because encrypted data is decrypted when the data is read from disk, so it resides in unencrpted form in system memory. If data from system memory spills to tempdb (eg because of explicitly created temp tables, version store, sort or hash operations on large amounts of data, etc) writing out the unencrypted data would form a security risk. In order to make TDE 100% reliable, tempdb has te be encrypted as soon as one or more user database is encrypted.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
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