Sql-server – AlwaysOn with TDE

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.

Elijah. There's two separate questions here:

1. Is DTC supported with AlwaysOn Availability Groups?

You're using SQL Server 2012, and according to Microsoft's Documentation, that answer is no. I totally understand that you want to try it anyway, but keep in mind that you're now putting something into production that Microsoft simply will not support, AND you're using two separate niche features together (AGs and DTC). If anything whatsoever goes wrong, you're going to be in a world of hurt. This just isn't something I'd ever even think about trying in production.

Keep in mind that if your managers find out that you deployed something Microsoft specifically says in big letters, "YOU CAN'T DO THIS," and you have any kind of outage where you have to call Microsoft for support, you're going to have some ugly explaining to do.

Technically, DTC is supported starting with SQL Server 2016 SP2 and later, but it just means that you can pick which database loses data on failover, and the application has no idea data was permanently lost. That's not what a normal database administrator would call DTC support.

2. How should DTC be configured in a multi-node, multi-subnet cluster?

Read Allan Hirt's post on configuring DTC with multiple instances of SQL Server in a cluster, and make sure to read all of the links in the post as well.