Enable TDE for existing AlwaysOn databases

Question

I need to do a little benchmarking of AlwaysOn, both with and without TDE enabled. All the instructions I see cover adding encrypted databases to an availability group, but I see no mention of enabling encryption for a database already in an AG.

Can I just restore the server certificate to all nodes, then enable TDE from the primary? I'm hoping I don't need to remove the database from the AG, encrypt it, then reinitialize all nodes, as the database is around 100 GB. We can do that if needed, but it'll certainly slow down our testing a bit.

Update

I just tried this on some lab VMs. I was able to create a certificate at the primary, restore it on all the secondary nodes, then create a database encryption key at the primary using this certificate. All I had to do after that was ALTER DATABASE AdventureWorks SET ENCRYPTION ON at the primary server, and after a minute or two, sys.dm_database_encryption_keys showed encryption_state = 3 at all nodes.

So, followup question: Is there any reason to think that I shouldn't use this procedure? SQL Server appears to be happy with the results, but I haven't seen this approach discussed anywhere. When dealing with encryption and high-availability matters, I don't want to leave things to chance.

Answer 1

In the absence of any other input, here's what I was able to deduce. Short story: this appears to work fine.

As far as I can see, enabling TDE for a database is a logged operation, but the actual data encryption is not logged (otherwise the transaction log would grow considerably, which it doesn't). I assume there's something like the differential change map, or a flag in the page headers to indicate if a page is encrypted or not, and the storage engine just chews through everything until it's fully encrypted.

When I enable TDE at the primary database, the secondary database ends up encrypted too (I have verified this with a hex editor). While encrypting is in progress, the view sys.dm_database_encryption_keys indicates percent_complete for all the secondary servers is 0, and this value never increases. The primary server updates this column normally. All the replicas tend to finish encrypting at their own pace, at which point encryption_state changes to 3. Seems like everything works normally once it's finished.