Search code examples
sqlsql-serverencryptiontdealwayson

AlwaysOn with TDE and Downtime

I'm fairly new to using TDE encryption with AlwaysOn Availability Groups. I have a few questions regarding application of the TDE certificate on a live database instance:

  • What kind of downtime have you experienced for enabling encryption on a SQL server database?
  • Would failing over to the secondary when applying the encryption be a valid tactic or is it safe to be applied while live?

Any help is appreciated. Thank you.

Solution

  • I've enabled TDE a couple of times. The database was available while the encryption was in progress. It is I/O (it has to read the entire database and write it back out) and CPU (you're doing encryption) intensive, so you may still want to do it in a maintenance window. You can look at sys.dm_database_encryption_keys to track progress and status.

    As far as failing over to the secondary to do the encryption, I don't understand the motivation. Specifically, once you fail over, what was the secondary is now the primary and whatever you were trying to avoid by doing it on the primary isn't possible to avoid.

    One thing to mention about the secondary though is make sure that the certificate that you're using to enable TDE exists on all nodes in your AG. My suggestion would be to set this up in a non-production environment and test your failover to make sure it's good to go.