TDE and Tempdb

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jayakumar Krishnan » TDE and Tempdb

23 posts, Page 1 of 3

1 2 3 »»»

TDE and Tempdb

Rate Topic

Display Mode

Topic Options

Author

Message

Jayakumar Krishnan

Posted Monday, June 28, 2010 10:05 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 5:52 PM
Points: 415, Visits: 738

Comments posted to this topic are about the item TDE and Tempdb

Thanks
Jay
http://www.sqldbops.com

Post #944364

Kari Suresh

Posted Monday, June 28, 2010 11:36 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 01, 2013 11:12 PM
Points: 248, Visits: 372

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

Post #944385

tommyh

Posted Monday, June 28, 2010 11:37 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476, Visits: 1,943

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)

Post #944387

vk-kirov

Posted Tuesday, June 29, 2010 12:25 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 3,192, Visits: 4,151

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 Smile

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.

Post #944408

Hugo Kornelis

Posted Tuesday, June 29, 2010 3:09 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,244, Visits: 7,062

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

Post #944494

DugyC

Posted Tuesday, June 29, 2010 4:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:25 AM
Points: 1,165, Visits: 731

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"

Post #944523

chris.reynolds

Posted Tuesday, June 29, 2010 4:43 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 08, 2012 5:41 AM
Points: 76, 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.

Post #944532

mzettler

Posted Tuesday, June 29, 2010 6:18 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 1:55 PM
Points: 76, Visits: 130

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?

Post #944574

SQLBalls

Posted Tuesday, June 29, 2010 6:46 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:34 PM
Points: 385, Visits: 611

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

Post #944597

Hugo Kornelis

Posted Tuesday, June 29, 2010 7:02 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,244, Visits: 7,062

mzettler (6/29/2010)

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

Post #944607

« Prev Topic | Next Topic »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions