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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/*Create databse on acceptable path to all Replicas*/ CREATE DATABASE EncryptedCrap ON PRIMARY ( NAME = 'EncryptedCrap', FILENAME = 'E:\Crap\EncryptedCrap.mdf') LOG ON ( NAME = 'EncryptedCrap_log', FILENAME = 'E:\Crap\EncryptedCrap_log.ldf'); /*Create key*/ CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'GreatP0stBrent!' GO /*Create cert*/ CREATE CERTIFICATE EncryptedCrapCert WITH SUBJECT = 'If you can read this I probably got fired.' |
Alright, cool. We did that. Now we have to get all up in our database and scramble its bits.
1 2 3 4 5 6 7 8 9 10 11 12 |
/*Get into database*/ USE EncryptedCrap GO /*Create database encryption key*/ CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE EncryptedCrapCert GO /*Turn encryption on*/ ALTER DATABASE EncryptedCrap SET ENCRYPTION ON |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
/*Back into master*/ USE master GO /*Backup cert to fileshare*/ BACKUP CERTIFICATE EncryptedCrapCert TO FILE = '\\Sqldc01\sqlcl1-fsw\NothingImportant\EncryptedCrap.cer' WITH PRIVATE KEY (FILE = '\\Sqldc01\sqlcl1-fsw\NothingImportant\EncryptedCrap.pvk' , ENCRYPTION BY PASSWORD = 'GreatP0stBrent!' ) GO :CONNECT SQLVM02\AGNODE2 USE master GO /*Set up password*/ CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'GreatP0stBrent!' GO /*Restore cert from share*/ CREATE CERTIFICATE EncryptedCrapCert FROM FILE = '\\Sqldc01\sqlcl1-fsw\NothingImportant\EncryptedCrap.cer' WITH PRIVATE KEY (FILE = '\\Sqldc01\sqlcl1-fsw\NothingImportant\EncryptedCrap.pvk', DECRYPTION BY PASSWORD = 'GreatP0stBrent!'); GO :CONNECT SQLVM03\AGNODE3 USE master GO /*Set up password*/ CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'GreatP0stBrent!' GO /*Restore cert from share*/ CREATE CERTIFICATE EncryptedCrapCert FROM FILE = '\\Sqldc01\sqlcl1-fsw\NothingImportant\EncryptedCrap.cer' WITH PRIVATE KEY (FILE = '\\Sqldc01\sqlcl1-fsw\NothingImportant\EncryptedCrap.pvk', DECRYPTION BY PASSWORD = 'GreatP0stBrent!'); GO :CONNECT SQLVM01\AGNODE1 USE master GO ALTER AVAILABILITY GROUP SQLAG01 ADD DATABASE EncryptedCrap GO |
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!
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?
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.
Glad to help!
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).”
In 2016 TDE plays nice with backup compression. https://blogs.msdn.microsoft.com/sqlcat/2016/06/20/sqlsweet16-episode-1-backup-compression-for-tde-enabled-databases/
Yep, already have the blog post written testing that out. Thanks.