5

Fair warning, I am very new to encryption with DBs. Right now we use the following statement to swap certificates when in the process backing/restoring DBs between servers:

ALTER DATABASE ENCRYPTION KEY REGENERATE 
    WITH ALGORITHM = AES_256 
    ENCRYPTION BY SERVER CERTIFICATE....

This is working perfectly fine but the issue is that the amount of time it takes to change the certificate through this statement. I've noticed that it can take more than an hour to change the certificate for DBs that are >100GB.

Is there a faster way to change TDE Certificates that anyone knows of?

7

Is there a faster way to change TDE Certificates that anyone knows of?

If you're actually needing to change the certificate that is doing the encryption (it protects the data) then the only faster way is to get a faster disk subsystem, new motherboard/cpus that support AESNI, and upgrade to SQL Server 2016.

If, however, you don't need to rotate the actual encrypting key but one of the protecting keys (Server Cert in this case, not the DEK) it's a very short mostly but not all metadata operation that can be accomplished via ALTER DATABASE ENCRYPTION KEY [Blah] ENCRYPTION BY SERVER CERTIFICATE [Blah2]. That will change the server certificate used to protect the DEK which will be extremely fast as the physical data does not need to be decrypted and encrypted again using the new DEK.

  • 1
    I'm not sure if I need to rotate the actual encryption key, I'm just picking up from existing code. Is there a way to verify by looking at the certificates? Thank you for your extensive answer! – BadgerBeaz Aug 11 '17 at 12:53
  • @BadgerBeaz By the code you posted, yes, you're actually decrypting and encrypting the data all over again (regenerating the key that protects the data). That's why I asked :) If you're just wanting to restore between servers, you can restore the db, change the server certificate used as I gave an example with, and then remove the original server cert. – Sean Gallardy Aug 11 '17 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.