June 29, 2010 at 10:47 am
Thanks for your comments and sorry for the confusion on the question, I took a negative meaning "great performance effect" as great performance degrade.
Thanks again for your comments...
Thanks
Jay
http://www.sqldbops.com
June 29, 2010 at 11:15 am
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
June 29, 2010 at 11:21 am
mattr-846241 (6/29/2010)
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
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.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
July 1, 2010 at 8:24 am
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?
July 1, 2010 at 8:38 am
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)
July 1, 2010 at 9:50 am
Steve Jones - Editor (7/1/2010)
You can't run TDE on laptops practically. It's in Enterprise Edition only (a mistake, IMHO)
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.
July 1, 2010 at 9:54 am
Sorry, yes, in developer as well. I was thinking about production level stuff. I think it ought to be in Standard, web, Express.
July 1, 2010 at 11:26 am
Festeron (7/1/2010)
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?
The is a great TDE tutorial on
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
And are you using TDE on any laptop-based SQL Servers?
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.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply