A commenter commented
That the “New AG Wizard” in SSMS 2017 had surfaced the Direct Seeding mode for AGs.
I was pretty psyched about this because I think it’s a great feature addition to AGs that can solve for a pretty big hump that people run into when they create databases regularly.
It used to be that when you created a new database for a new user, you had to take backups and restore them. This was automated when you first created an AG, but not afterwards. SQL wouldn’t take any proactive steps to make sure new databases got added — you had to script it yourself.
With Direct Seeding, the amount of scripting is reduced dramatically, as the synchronization process is moved behind the scenes.
You still have to:
- Create the database
- Take a full backup (can be to NUL)
- Add the database to the AG
GUI Goodness
It’s the first thing in the GUI now when you choose how you’re going to first sync up your AG.
And the Wizard now notifies you of your choice.
What comes in really handy here, is that if you script out your initial AG config (because this is a good script to have if you need to rebuild your AG in part or whole at some point), the option is baked in for you. You don’t have to go back and alter your AG to add Direct Seeding afterwards.
It even, quite helpfully, scripts in the new permission you need to apply to make Direct Seeding work.
Rehash
I haven’t gone back to test things from my previous posts about Direct Seeding. Things got a little weird with TDE, and Trace Flag 9567, which enables compression, showed incorrect values in Extended Events and DMVs.
Thanks for reading!
5 Comments. Leave new
I was playing with this the other day while training someone on how to add databases to availability groups. Somehow I managed to completely lock up the primary instance, and I couldn’t even get a query to return on the DAC. Fortunately this was QA so I kicked the requisite boxes and got things running again, but at that point I had to join the databases the old way because seeding failed with “Seeding Check Message Timeout”.
Hopefully I can get confident enough with this to actually use it in production, because this is an awesome feature. I’m really excited to see what people do with it.
Hi,
I have one query regarding Distributed AG. All examples I have seen for Distributed AGs are for Automatic Seeding. I have question of how to set Distributed AG with using Manual Seeding as I don’t want to use Automatic Seeding for large databases.
Sure, for questions, head to a QA site like DBA.stackexchange.com.
Thanks
Seemingly random failures with direct seeing has continued to be something of a nemesis of yours, even after the most recent fixes in SP2 CU8 for SQL 2016. One trick is to set the seeding mode for the replica where seeding failed to manual, then back to automatic. That will often cause SQL to re-initialize seeding of the databases that failed the first time.