SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jayakumar Krishnan / TDE and Tempdb / Latest Posts

RE: TDE and Tempdb

SQLBalls — Thu, 01 Jul 2010 11:26:40 GMT

[quote][b]Festeron (7/1/2010)[/b][hr]I'd be interested in your answer to these questions:If you're using TDE in a production environment, what do you think you are protecting yourself from?[/quote]The is a great TDE tutorial on[url]http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/06/24/new-in-sql-2008-transparent-data-encryption-overview.aspx[/url]What you will discover from it is all of the data stored in your MDF file is viewable in a text editor. So if you have a tape backup go missing, if a hacker gains access to your hard drive, or if a disgruntaled or careless employee takes a copy of a backup and has it on thier local computer and the computer gets stolen your data is at risk.Might not seem like a big deal, but banks and credit card companies have requirements on them to store PII, Personal Identifiable Information, in an encrypted format. Typically this involves purchasing expesive 3rd party tools. SQL 2008 doesn't require a 3rd party tool to accomplish this so job done and money saved.One other thing to keep in mind, as this is the area I work in, in the Government if you loose a database that has PII on it, you have to inform Congress and send out letters to everyone whose information was in that database letting them know that thier information may be compromised.TDE works on the MDF & Log files as well as the Backup Files. It is transparent so it does not encrypt data on the instance nor does it encrypt files in memory. So if someone has access to your database they can see what you can see.Once again any way you look at it this is bad for your career, as you don't want to be interviewing for a job saying I just worked at XYZ, and the interviewer go "Oh weren't they just in the news because a lot of PII got stolen froom there?"So this is a CYA thing on a multitude of levels, for a business, for customers, and for the DBA's[quote]And are you using TDE on any laptop-based SQL Servers?[/quote]TDE is also enabled on SQL 2008 Developer Edition. There are drive level encryptions that should be used on Hardware where senative info would be stored. So if you needed to have a local copy of a sensitive database you should have an encrypted hard drive, and if it is a copy of a prod database that uses TDE you would have to have the certificates on that instance from production to restore a copy of the database.I use TDE on my home laptop, but that is cause I work with it. But I would never put production data on a laptop, I would hate to be like the guy at the VA that lost the laptop with millions of Veterans private info on it, he probably had every enlisted man from generals to privates trying to get a peice of him.

RE: TDE and Tempdb

Steve Jones - SSC Editor — Thu, 01 Jul 2010 09:54:38 GMT

Sorry, yes, in developer as well. I was thinking about production level stuff. I think it ought to be in Standard, web, Express.

RE: TDE and Tempdb

UMG Developer — Thu, 01 Jul 2010 09:50:08 GMT

[quote][b]Steve Jones - Editor (7/1/2010)[/b][hr]You can't run TDE on laptops practically. It's in Enterprise Edition only (a mistake, IMHO)[/quote]I thought TDE was in the Developer edition as well, which is what I think gets run on laptops a lot.TDE ensures that not only your data and logs files are secure while "at rest", your backup files are secure as well.

RE: TDE and Tempdb

Steve Jones - SSC Editor — Thu, 01 Jul 2010 08:37:59 GMT

TDE protects from physical access to the files if SQL is down. If you detach/attach files, they are safe.You can't run TDE on laptops practically. It's in Enterprise Edition only (a mistake, IMHO)

RE: TDE and Tempdb

Festeron — Thu, 01 Jul 2010 08:24:37 GMT

I'd be interested in your answer to these questions:If you're using TDE in a production environment, what do you think you are protecting yourself from?And are you using TDE on any laptop-based SQL Servers?

RE: TDE and Tempdb

SQLBalls — Tue, 29 Jun 2010 11:21:34 GMT

[quote][b]mattr-846241 (6/29/2010)[/b][hr]One way performance could be affected is if you use temporary tables. You may have a database which has a stored procedure which uses a temporary table. I think there are other ways that a database will use the tempdb, but I don't know them offhand.Cheers,Matt[/quote]Hey Matt, Your right Temp Tables as well as Table Variables, SQL Internal Work tables, the SQL version store, re-indexing jobs that specify sort in tempdb, and many other things are done within the Tempdb for not just TDE databases but for all databases, as well as for SQL internal processes. Just to repeate though 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. Hopefully this will get expanded by Microsoft or on some SQL blogs some where, as there is not a lot of documentation out there on this overall effect right now. But you were right on in the suggestion that Temp tables could be effected but they would need to be so large that they spill onto the disk as if they are in the memory they should be unaffected.

RE: TDE and Tempdb

mattr-846241 — Tue, 29 Jun 2010 11:15:55 GMT

One way performance could be affected is if you use temporary tables. You may have a database which has a stored procedure which uses a temporary table. I think there are other ways that a database will use the tempdb, but I don't know them offhand.Cheers,Matt

RE: TDE and Tempdb

Jayakumar Krishnan — Tue, 29 Jun 2010 10:47:46 GMT

Thanks for your comments and sorry for the confusion on the question, I took a negative meaning "great performance [b]effect[/b]" as great performance degrade. Thanks again for your comments...

RE: TDE and Tempdb

UMG Developer — Tue, 29 Jun 2010 10:23:29 GMT

Nice question, thanks!I think little bits like this will help people learn and understand new features like TDE so they are better prepared when they actually need to use it.

RE: TDE and Tempdb

SQLRNNR — Tue, 29 Jun 2010 09:38:56 GMT

Thanks for the question.As per the use of the term "Great" - it was gone by the time I answered but here is my 2 bits. The use of the word "great" most likely meant "significant." Granted that definition is also debatable since 3-5% may or may not be a significant amount depending on your environment.

RE: TDE and Tempdb

Daniel Bowlin — Tue, 29 Jun 2010 09:13:24 GMT

Good question. I am glad the great was removed by the time I got to it.

RE: TDE and Tempdb

Steve Jones - SSC Editor — Tue, 29 Jun 2010 08:44:30 GMT

I have removed "great" from the question as it does depend. There is some performance impact, but how much is debatable.

RE: TDE and Tempdb

Bradley Deem — Tue, 29 Jun 2010 08:28:15 GMT

Thanks for the question, I needed to brush up on my TDE knowledge. Admittedly, the "great" caused me to pause, but I still got it right. As Hugo said yesterday, the QOTD is for learning not for points and for that I thank you.

RE: TDE and Tempdb

Hugo Kornelis — Tue, 29 Jun 2010 07:02:16 GMT

[quote][b]mzettler (6/29/2010)[/b][hr]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?[/quote]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 [i]has[/i] te be encrypted as soon as one or more user database is encrypted.

RE: TDE and Tempdb

SQLBalls — Tue, 29 Jun 2010 06:46:24 GMT

I agree with everyone else.I went to the link on MSDN to read about this, and it says basically that it "[b][u]might[/u][/b]" 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.

RE: TDE and Tempdb

mzettler — Tue, 29 Jun 2010 06:18:09 GMT

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?

RE: TDE and Tempdb

chris.reynolds — Tue, 29 Jun 2010 04:43:58 GMT

[quote][b]DougieCow (6/29/2010)[/b][hr]I also was caught out by the use of the word "great" which I interpreted as a "positive" performance impact.[/quote]Me too. Great is, perhaps, too subjective a word.

RE: TDE and Tempdb

DugyC — Tue, 29 Jun 2010 04:10:34 GMT

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.

RE: TDE and Tempdb

Hugo Kornelis — Tue, 29 Jun 2010 03:09:58 GMT

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: [url=http://www.simple-talk.com/sql/database-administration/transparent-data-encryption/]http://www.simple-talk.com/sql/database-administration/transparent-data-encryption/[/url])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 [b]some[/b] performance effect for other unencrypted databases", it would be a great question.

RE: TDE and Tempdb

vk-kirov — Tue, 29 Jun 2010 00:25:39 GMT

[quote]This could have a great performace effect for the other unencrypted databases[/quote]I agree with [b]tommyh[/b], 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: [url]http://www.databasejournal.com/features/mssql/article.php/3815501/Performance-Testing-SQL-2008s-Transparent-Data-Encryption.htm[/url]Rob Garrison, the author of the article, emulated an OLTP environment and found that the average TDE overhead was [b]6.36%[/b]. 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.

RE: TDE and Tempdb

tommyh — Mon, 28 Jun 2010 23:37:46 GMT

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)

RE: TDE and Tempdb

Kari Suresh — Mon, 28 Jun 2010 23:36:00 GMT

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

TDE and Tempdb

Jayakumar Krishnan — Mon, 28 Jun 2010 22:05:31 GMT

Comments posted to this topic are about the item [B]TDE and Tempdb[/B]