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 Setup and Administration Scripts Expand / Collapse
Author
Message
Posted Tuesday, March 8, 2011 12:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 8:59 AM
Points: 42, Visits: 178
Comments posted to this topic are about the item TDE Setup and Administration Scripts
Post #1074613
Posted Tuesday, March 8, 2011 12:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:44 PM
Points: 131, Visits: 801
Just for the records so that not everybody has to google it who isn't familiar with this feature: TDE = Transparent Data Encryption
Nice article though.
Post #1074620
Posted Tuesday, March 8, 2011 6:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:16 PM
Points: 196, Visits: 1,082
Just a warning to those who would like to turn on TDE on servers that host some other databases along with the db you want to encrypt: if some of other DB's heavily utilize TEMPDB ( for example, using a lot #temp tables, doing a lot sorts, etc...), turning on TDE may kill the performance, yes, KILL, literally, the server would not responce due to the chance that the tempdb would go mad and refuse to work.

we had this last week in our DEV server and i couldn't even run sp_who2 when it happened.
RESTART Server ( tried to restart the SQL Server service and it hang; had to reboot the server) and turn off TDE...and then Restart Server cleaned up the chokeness.


Make sure you test enough before putting it to PROD...or leaving those encrypted DB'S on one box may be a better idea.
Post #1074754
Posted Tuesday, March 8, 2011 7:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 8:59 AM
Points: 42, Visits: 178
By chance my site only has one database that uses TDE with no other application databases on the same instance. Definitly try any change in DEV or TEST before PROD. A silver bullet restart of SQL might help if you have some databases using TDE and some not using TDE. It is the case that you have to restart SQL if you run the RemoveTDE script so that tempdb get receated properly so perhaps this is true when you first set it up too.

Not sure that performance would be hit massively re use of tempdb. Clearly there is no such thing as "something for nothing" so there is a performance hit but I believe it is not prohibitive unless your server is already operating near the bounds of its capacity. I did attend a Kevin Guinn presentation on TDE performance impact at SQL PASS 2009 which presented results from extensive performance comparisons of TDE configured databases against non TDE databases for sample operations. The results did not deter me from going ahead with my intended use of TDE. That said my particular scenario is not a heavily used database. This seems to be the basis for that presentation:

http://www.dell.com/downloads/global/solutions/Dell_SQL2008_EE_Encryption_and_Compression.pdf

Post #1074819
Posted Tuesday, March 8, 2011 9:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 881, Visits: 1,007
Nice article, thanks. I'm about to implement TDE on a couple of servers so very timely.

As you noted:

Sean Elliott (UK) (3/8/2011)
...my site only has one database that uses TDE with no other application databases on the same instance.


Question: How do you enable TDE for a second database using your scripts?

Thanks.



Post #1074924
Posted Tuesday, March 8, 2011 11:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:25 PM
Points: 117, Visits: 241
liebesiech
You have the best comment here!!!!!

What kind of a specialist does not clarify abbreviations at the top of such an interesting article!!!!!
Post #1075016
Posted Tuesday, March 8, 2011 4:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 8:59 AM
Points: 42, Visits: 178
I did not want to write yet another article about TDE that tells you lots of complicated theory and background but then does not actually tell you how to use it for real. If you do not know what TDE is an acronym for then this is not the place to start.

I read several of these and still did not understand what you were supposed to do or really why there were so many levels of protection. It was only when I tried to do it for real that I gradually understood properly.
Post #1075229
Posted Tuesday, March 8, 2011 4:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 8:59 AM
Points: 42, Visits: 178
I guess my scripts as they are only work for one database comprehensively

Quick answer - everywhere where there are commands affecting XX_DATABASE_XX copy and paste them as many times as required to include all the databases you require to use TDE.

If there are many such databases you could use master.dbo.sp_MSForEachDb to hit all relevant databases in a loop replacing XX_DATABASE_XX with [?] or '?'. You can see extensive use of sp_MSForEachDb in my other article about DDL auditing - gets slightly tricky if you need any quote characters in the command.

Not all the scripts do actually refer to a particular datadase so not all need changing. An example of one that would need changing is RemoveTDE.sql. For example part of it could be enhanced to:

exec master.dbo.sp_MSForEachDb
'
if ''?'' != ''tempdb''
begin
if exists
(
select 1 from sys.dm_database_encryption_keys
where DB_NAME(database_id) = ''?''
)
begin
while not exists
(
select encryption_state from sys.dm_database_encryption_keys
where DB_NAME(database_id) = ''?'' and encryption_state = 1
)
begin
set @DateStr = convert(varchar, getdate(), 120)
raiserror(''%s Waiting 5 seconds for database encryption to complete'', 0, 0, @DateStr) with nowait
waitfor delay ''00:00:05''
end
end
end
'

Post #1075231
Posted Tuesday, March 15, 2011 3:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,163, Visits: 2,191
Great article and scripts, thanks!
Post #1078704
Posted Tuesday, March 15, 2011 4:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 8:59 AM
Points: 42, Visits: 178
Comments like this make it worthwhile so thanks!
Post #1078707
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse