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.

Log du jour

Log du jour

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.

I failed college algebra, again.

I failed college algebra, again.

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.

Ungoogleable errors.

Ungoogleable errors.

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.

Attempting Helpful.

Attempting Helpful.

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.

Feature Complete.

Feature Complete.

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!

Previous Post
Availability Group Direct Seeding: Extended Events and DMVs
Next Post
[Video] Office Hours 2016/06/29 (With Transcriptions)

8 Comments. Leave new

  • My take-away for your recent 3 posts is: Direct Seeding looks like a massive PITA and a simple backup/restore is such a noddy, easy thing for a DBA, why would we bother with all this extra hassle? It strikes me that you and your pointed-headed henchmen at Ozar towers are doing the testing Microsoft clearly couldn’t be arsed to do.

    What gives, Darling?

    • Erik Darling
      July 1, 2016 8:48 am

      Sort of. It depends on the size and timing of the backup, I guess? I think it’s cool that there’s some automation here. This feature is clearly just an embryo, and I’m excited (cautiously optimistic) to see where it goes. Just like AGs in 2012, there’s a long way to go.

  • Another great post. Thank you for the invaluable, and highly entertaining, information. This has helped in some of my planning conversations with upper management.

  • You have done the certificate, but what about restoring the master key to the secondary?

    • Sorry for the late response. According to MS, that isn’t necessary.

      From here:

      “You do not need to have the exact same database master key on each SQL instance. In other words, you do not need to back up the DMK from the primary and restore it onto the secondary. As long as each secondary has a DMK then that instance is prepared for the server certificate(s).”

  • Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.