Notes From A SQL 2017 Azure Install – Automatic Seeding Of An Availability Group TDE Database

One of the neat features introduced with SQL Server 2016 was the automatic seeding of availability groups.  And while it seems to work pretty well in most cases, if you are using TDE with your databases then all bets are off!  Here’s an article from the folks over at Brent Ozar Unlimited where they played around with it and couldn’t seem to get it to work.

However, with SQL Server 2017 that doesn’t seem to be the case.  I’m not sure if this was fixed in 2017 RC, or one of the updates (I tested on 2017 CU 3), but for me direct seeding of availability group TDE databases is working correctly.

Using automatic seeding with Availability Groups is pretty simple, you set it up following this tutorial (again, from the fine folks at Brent Ozar Unlimited), and make sure you have SEEDING_MODE = AUTOMATIC set for each of your Availability Group nodes.  Once you have the SEEDING_MODE option set, all you have to do is add a database to your Primary Availability Group server, and it will automatically create and populate that database on all of your Secondaries.

The process seems to go pretty quickly.  When I add a new database to my Primary server, it takes about a minute to have it replicated and brought online on the Secondary.  Admittedly, I’m testing with small databases, around 5-10GB.  I feel sure it would take longer on larger databases.


(Visited 247 times, 1 visits today)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.