Availability Group Direct Seeding: TDE’s Frenemy

From the Mailbag

In another post I did on Direct Seeding, reader Bryan Aubuchon asked if it plays nicely with TDE. I’ll be honest with you, TDE is one of the last things I test interoperability with. It’s annoying that it breaks Instant File Initialization, and mucks up backup compression. But I totally get the need for it, so I do eventually get to it.

The TL;DR here

Is that if you encrypt a database that’s already taking part in a Direct Seeding relationship, everything is fine. If you already have an encrypted database that you want to add to your Availability Group, Direct Seeding has a tough time with it.

I don’t think this is an outright attempt to push people to AlwaysEncrypted, because it has a lot of limitations.

Let’s walk through this

Because I love reader sanity checks, here we go. Microsoft tells you how to add a database encrypted with TDE to an existing Availability Group here.

That all sounds good! So let’s follow directions. We need a database! We also need a password, and a certificate. Alright, we can do this. We’re competent adults.

Alright, cool. We did that. Now we have to get all up in our database and scramble its bits.

SQLCMD Appreciation Header

Few things in life will make you appreciate SQLCMD mode like working with Availability Groups. You can keep your PowerShell. $.hove-it; I’m with SQLCMD.

Stick with me through the next part. You may have to do this someday.

What did we do?

Exactly what we did. We backed up our certificate to a network share, created a private key for it, and then on two replicas we created master passwords, and created certificates using the backup of our certificate from the primary. We did this in one SSMS window. Magical. Then we added our encrypted database to the Availability Group.

If this database weren’t encrypted, everything would probably go just fine. I say probably because, you know, computers are just the worst.

But because it is encrypted, we get some errors. On our Primary Replica, we get normal startup messages, and then messages about things failing with a transient error. Not sure what a transient error is. It forgot to tie its shoelaces before running to jump on that freight car.

On our Replicas, we get a different set of messages. Backup failures. Database doesn’t exist. More transient errors. This time you left an open can of pork beans by the barrel fire.

Over in our Extended Events session that tracks automatic seeding, we get an error code! searching for it doesn’t really turn up much. New features. Good luck with them.

One bright, shiny star of error message-y goodness shows up in our Physical Seeding Extended Event session. Look at all those potentially helpful failure codes! An individual could get a lot of useful information from those.

If only you weren’t being laughed at by the Gods of HA/DR. Some of the physical_seeding Extended Events have values here, but none of the automatic seeding ones do.

As of now

I don’t have a work around for this. The alternatives are to decrypt, and then re-encrypt your database after you add it, or add it the old fashioned way. Maybe something will change in the future, but as of now, these don’t appear to be compatible.

I’ve opened a Connect Item about this. I’d appreciate votes of the upward variety, if you feel so inclined.

Thanks for reading!