Can I set TDE Encryption off while my db actively in use?

  • 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 😀

  • 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 😀

  • Yes, the encryption or decryption happens in the background.

  • 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 😀

  • 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.

  • 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 😀

  • 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.

  • sqlsurfing - Tuesday, March 5, 2019 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?

    Yes.

  • jasona.work - Tuesday, March 5, 2019 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.

    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 9 (of 9 total)

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