TDE Setup and Administration Scripts

  • Karlier,

    Are you waiting until the DMV, sys.dm_database_encryption_keys, shows that the decryption has completed before restarting the instance? (Both encryption and decryption are done as a background process.)

  • Yes, the RemoveTDE.sql script take control of that. Am I the only one that got "suspect" DB when run RemoveTDE.sql and "restart" the instance? :blink:

  • I have not encountered this problem and do not want to set out to cause it either! It is probably a SQL Server bug.

    I see that there is some evidence that once you have setup TDE on a server you may need to keep the server certificate and the Database Master Key (DMK) in the MASTER database even though you no longer have any databases setup for TDE. So if you have this problem with databases becoming suspect on restart you should try putting the server certificate and DMK back. Not really a problem to have them there if not being used.

  • If you need to remove TDE you need to also do what it says here:

  • I read it a few hours ago. It is a really helpful article. The BOL should state those recommendations.

    Thanks for posting it!

  • Or this obvious bug should be fixed! 😛

  • Thank you for the article, it was interesting.

    If I might add a couple of things, TDE is only available on enterprise (or developer) feature. Also, you mentioned "no peeking" at the HR password. by encasing your scripts in a GUI you shield the password from other eyes. Of course, people would have to trust you to not make your GUI log the password somewhere, but that at least removes the temptation to "peek".

    Timothy A Wiseman
    SQL Blog:

  • Thanks for a great article. I tried it out and it works OK.

    Now I was just wondering about backups. We use a third party tool for backups which does its own encryption while taking the backup (AES128). How do you think using TDE will affect restores of databases backed up using this tool?

    Br. Kenneth Igiri
    All nations come to my light, all kings to the brightness of my rising

  • good article but unfortunately it makes the same mistake regarding master keys that others make.

    To restore a TDE protected database to another server you do not need to backup and restore the database master key held in the master database. if a database master key doesn't already exist on the target server create one before restoring the certificate backup from the source server, but it is in no way tied to the certificate or the restore.


    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Many thanks for this article and scripts - would like to add this though, make sure you have no long running transactions on your database that you want to encrypt before encrypting. DBCC OPENTRAN; on the relevant database will let you know the longest one. Didn't find this out until we run it on our Production database after tests etc - 18 hours later and no movement - once i killed off that transaction that had be open (for days!) completed in 30 mins.

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply