March 4, 2019 at 11:28 pm
We have a DB about 45GB (32gb compressed-backed up) that has TDE setup, I need to remove encryption so I can backup and restore it to another server in the cloud where I don't have ability to restore the keys thus "removing TDE encryption " is necessary. Does someone know if we can run this while db is actively in use? I tested this with a testdb with some simple update and inserts..seems to work but rather confirm 100%
1. set encryption off
2. drop encryption key
In order to reduce down time I am wondering if I can set encryption off during the day? (step1) ... as it takes awhile... up to 20-25 mins to complete. before I can proceed to step 2, dropping encryption key is quick though
step 1:
use master
go
ALTER DATABASE [MYDBTEST] SET ENCRYPTION off;
However after running this I notice background process
a session for ALTER DATABASE is running on "MYDBTEST"
It takes while before it completes-- must wait.... once background process is done proceed to step 2
step 2:
USE [MYDBTEST];
GO
DROP DATABASE ENCRYPTION KEY;
GO
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
March 5, 2019 at 9:52 am
Just checking if any one knew the answer to this again 🙂 I'd like to reduce my downtime... most of the time TDE is set it and forget it....exception keeping pw and keys safe 🙂
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
March 5, 2019 at 10:23 am
Yes, the encryption or decryption happens in the background.
March 5, 2019 at 12:21 pm
Awesome thanks Steve! I met you once at PASS a couple years ago at Red-Gate booth- you were really nice-- as well online too answering questions 😀
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
March 5, 2019 at 1:39 pm
Thank you and happy to help.
Note that you will get some logging as pages change and they are decrypted. If you encrypt again, it's online, but more logs.
Good luck.
I might suggest restoring this locally somewhere, then removing encryption, and taking a backup. That would prevent the need to re-encrypt.
March 5, 2019 at 2:41 pm
Note that you will get some logging as pages change and they are decrypted. If you encrypt again, it's online, but more logs.
Good luck.
I might suggest restoring this locally somewhere, then removing encryption, and taking a backup. That would prevent the need to re-encrypt
Hi Steve,
When you mean 'online' you mean it will be 'online' actively usable by clients/apps hitting the db?
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
March 5, 2019 at 6:01 pm
Probably a bit too late, but yes, the DB will be online and available to the clients while the decryption is running. I need to do this occasionally at work to provide a copy of a database to developers and have never had a complaint.
You can check the status of the decryption by querying sys.dm_database_encryption_keys (I'll usually join to sys.databases to get the database name.) See the query below:USE [MASTER];
GO
SELECT DB.NAME
, DEK.ENCRYPTION_STATE
, DEK.PERCENT_COMPLETE
FROM SYS.DM_DATABASE_ENCRYPTION_KEYS AS DEK
FULL JOIN SYS.DATABASES AS DB
ON DB.DATABASE_ID = DEK.DATABASE_ID
ORDER BY DB.NAME
(Pretty sure I've got that right, I'm going from memory)
You can of course, filter it down to just the database you're interested in easily enough.
March 6, 2019 at 9:07 am
sqlsurfing - Tuesday, March 5, 2019 2:41 PMNote that you will get some logging as pages change and they are decrypted. If you encrypt again, it's online, but more logs.
Good luck.
I might suggest restoring this locally somewhere, then removing encryption, and taking a backup. That would prevent the need to re-encrypt
Hi Steve,
When you mean 'online' you mean it will be 'online' actively usable by clients/apps hitting the db?
Yes.
March 6, 2019 at 12:30 pm
jasona.work - Tuesday, March 5, 2019 6:01 PMProbably a bit too late, but yes, the DB will be online and available to the clients while the decryption is running. I need to do this occasionally at work to provide a copy of a database to developers and have never had a complaint.You can check the status of the decryption by querying sys.dm_database_encryption_keys (I'll usually join to sys.databases to get the database name.) See the query below:
USE [MASTER];
GOSELECT DB.NAME
, DEK.ENCRYPTION_STATE
, DEK.PERCENT_COMPLETE
FROM SYS.DM_DATABASE_ENCRYPTION_KEYS AS DEK
FULL JOIN SYS.DATABASES AS DB
ON DB.DATABASE_ID = DEK.DATABASE_ID
ORDER BY DB.NAME
(Pretty sure I've got that right, I'm going from memory)
You can of course, filter it down to just the database you're interested in easily enough.
Thanks Jasona! Cool query!
Wow that's amazing you have the system catalog memorized! ....I usually copy paste from my notes everytime :-/ (My external memory ha)
Ive been using these to check I properly removed encryption .. never looked at all the columns seems like it includes PERCENT_COMPLETE, thank you!
select * from sys.certificates
go
select * from sys.dm_database_encryption_keys
go
select
database_name = d.name,
dek.encryptor_type,
cert_name = c.name
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;
Also thanks Steve for the reply too!! 🙂
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply