Today's post is in response to this month's TSQL-Tuesday, kindly hosted by MATTHEW MCGIFFEN, a gent who I've learnt many things from his informative posting.
To be honest, the case isn't that curious, just SSMS and also the system views/DMVs being a little bit misleading in relation to TDE, and not having enough information in previous versions.
So, for whatever reason, you have to turn encryption off on your DB.
ALTER DATABASE [MySecureDB] SET ENCRYPTION OFF
After a while, you try to remove the Key from the Database
DROP DATABASE ENCRYPTION KEY
Msg 33105, Level 16, State 1, Line 43
Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key.
Eek.
The process of Encryption and Decryption isn't an instantaneous one, SQL has to move through all pages and perform the process. But you know your onions, and left it for a long time, and the database isn't that large.
Lets have a look at what's going on...
There's Treachery Afoot!
Take a look at the GUI and DMVs ... SSMS says not encrypted.
Lies...
Hmmmm, ok, to the Batcave!
SELECT db.name, db.is_encrypted,dm.encryption_state,dm.percent_complete
FROM sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id
WHERE DB.Name='MySecureDB'
Great,
sys.databases shows is_encrypted as '0' (which may explain SSMS saying no encryption) ... more lies...
sys.dm_database_encryption_keys shows percent_complete as 0. ... simply politician levels of falsehood going on now...
sys.dm_database_encryption_keys shows encryption_state as 5 ...At least something is being honest.
From learn.microsoft.com
0 = No database encryption key present, no encryption
1 = Unencrypted
2 = Encryption in progress
3 = Encrypted
4 = Key change in progress
5 = Decryption in progress
6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)
Say what now??
So immediately we have a bit of a mismatch - sys.databases says it's not encrypted, (which is encryption_state 1 in sys.dm_database_encryption_keys, but sys.dm_database_encryption_keys has the true state of 5, but a percentage value that implies this is going to take a while.
So according to that information, do we wait? Peering mindlessly at the percent_complete counter in the hope it moves?
Take a look in Sys.dm_exec_requests, when a decryption/encryption is in progress, you should see something with the Command of 'ALTER DATABASE ENCRYPTION KEY':
If you don't, then something is bost, broke, knackered, busted, kaput, shot with the encryption/decryption process.
What's going on here then Mr. Holmes?
Any disruption to the process will effectively break it, ie.. if the database goes offline, or put into single user mode etc, anything to cause the system spid to terminate, then you are left in this situation when it suggests its, but it isn't.
Thankfully, the resolution is simple, just run the TSQL that you could use to do this in the first place. The GUI is now not your friend, so...
ALTER DATABASE [MySecureDB] SET ENCRYPTION OFFAnd then the process starts back up again, not even at the beginning, at the point at which it failed. Check out the DMV again, you'll see the percent counter far further along than it would be if starting from scratch., ie... not starting again from zero.
So SQL knew it was partially complete, it just didn't want to tell you... thanks for that.
So what about versions of SQL when Arthur Conan the Authorian wasn't around?
Doing this on SQL 2019, and the sys.dm_database_encryption_keys DMV has many more columns, and for this situation you can at least get an idea of what's going on. Or not going on in this case....
Hurrah! Detail, lovely detail around what the process is doing!
BUT, all other detail is as above. SSMS will show the DB as not encrypted, the DMV percent_complete column will show as zero (when its not).
The exact same behaviour also happens in reverse, ie... if the DB is being encrypted and the process gets interupted, the values between DMV, system views and SSMS don't agree on the state fully which may lead to confusion.
Again, as soon as you run the ALTER DATABASE command to SET ENCRYPTION OFF, it then carries on from where it was interupted, so even on SQL 2019 have a little bit of caution with the fields in your scripts, and sometimes, just sometimes, don't believe what SSMS is showing you.
Thanks for reading.
Dr Rodson.