SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TDE Setup and Administration Scripts


TDE Setup and Administration Scripts

Author
Message
Sean Elliott (UK)
Sean Elliott (UK)
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 181
Comments posted to this topic are about the item TDE Setup and Administration Scripts
liebesiech
liebesiech
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 865
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.
;-)
DBA in Unit 7
DBA in Unit 7
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1499 Visits: 1124
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.
Sean Elliott (UK)
Sean Elliott (UK)
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 181
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
TerryS
TerryS
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 1474
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.



John Doe-361624
John Doe-361624
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 249
liebesiech
You have the best comment here!!!!!

What kind of a specialist does not clarify abbreviations at the top of such an interesting article!!!!!
Sean Elliott (UK)
Sean Elliott (UK)
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 181
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. :-P

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.
Sean Elliott (UK)
Sean Elliott (UK)
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 181
I guess my scripts as they are only work for one database comprehensively :-D

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
'


UMG Developer
UMG Developer
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5444 Visits: 2204
Great article and scripts, thanks!
Sean Elliott (UK)
Sean Elliott (UK)
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 181
Comments like this make it worthwhile so thanks! Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search