Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

TDE and Tempdb Expand / Collapse
Author
Message
Posted Monday, June 28, 2010 10:05 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:03 PM
Points: 417, Visits: 761
Comments posted to this topic are about the item TDE and Tempdb

Thanks
Jay
http://www.sqldbops.com
Post #944364
Posted Monday, June 28, 2010 11:36 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:05 AM
Points: 248, 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
Post #944385
Posted Monday, June 28, 2010 11:37 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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
Posted Tuesday, June 29, 2010 12:25 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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.
Post #944408
Posted Tuesday, June 29, 2010 3:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 5,975, Visits: 8,236
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
Posted Tuesday, June 29, 2010 4:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, 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"
Post #944523
Posted Tuesday, June 29, 2010 4:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 8, 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
Posted Tuesday, June 29, 2010 6:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 20, 2013 3:09 PM
Points: 76, 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?
Post #944574
Posted Tuesday, June 29, 2010 6:46 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 2:26 PM
Points: 386, Visits: 626
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
Posted Tuesday, June 29, 2010 7:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 5,975, Visits: 8,236
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
Post #944607
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse