I must be getting old, two blog posts in the same year! 🙂
Anyway, I made some additional testing as part of a TDE (Transparent Data Encryption, that is) implementation and I noticed couple of interesting things when you want to remove the TDE completely from the server:
- When checking sys.databases, tempdb is shown as not encrypted (is_encrypted = 0, not 1, as one would expect).
You will get the correct status, though, under sys.dm_database_encryption_keys.
- A simple right-click and restart service would not work in this case. One must stop and then start the SQL Server service in order for the TDE to be completely removed
As a side note, I’m on SQL Server 2012 SP2, did not find the time to check other versions.
How to remove TDE, you might wonder? Well, the lovely guys at simple-talk have the answer for you, I’m not going to repeat it here.