I have been asked to "rotate" the Certs and Keys for a given database and the only way I knew of to accomplish this was to unencrypt the data first, using
DECRYPTION BY CERTIFICATE, create new certs and keys, and re-encrypt using the new cert and key.
I was then told that I could not have the data in an unencrypted state (readable to users) for any amount of time and the application could not be brought down to ensure security for any amount of time. Then I found
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD
My question is, will this
REGENERATE accomplish the "rotation" of certs and keys (create new ones, which I think it will), and if so, will it leave the data in a "readable" state at anytime during the process?
SQL Server 2019 Standard Edition build 15.0.4083.2