Availability Group Direct Seeding: Extended Events and DMVs

As of this writing, this is all undocumented

I’m super interested in this feature, so that won’t deter me too much. There have been a number of questions since Availability Groups became a thing about how to automate adding new databases. All of the solutions were kind of awkward scripts to backup, restore, join, blah blah blah. This feature aims to make that a thing of the past.

There’s also not a ton of information about how this works, the option hasn’t made it to the GUI, and there may still be some kinks to work out. Some interesting information I’ve come across has been limited to this SAP on SQL blog post, and a Connect item by the Smartest Guy At SanDisk, Jimmy May.

The SAP on SQL Server blog post says that this feature uses the same method as Azure databases to create replicas; opening a direct data link, and Jimmy’s Connect item points to it being a backup and restore behind the scenes. The Extended Events sessions point to it being a backup and restore, so let’s look at those first.

Bring out your XML!

We’re going to need two sessions, because there are two sets of collectors, and it doesn’t make sense to lump them into one XE session. If you look in the GUI, there’s a new category called dbseed, and of course, everything is in the super cool kid debug channel.

New Extended Event Smell
New Extended Event Smell

Quick setup scripts are below.

Since this is so new

I haven’t quite narrowed down which are important and which yield pertinent information yet. Right now I’m grabbing everything. In a prelude to DBA days, I’m adding the StackOverflow database. With some session data flowing in, let’s figure out what we’re looking at. XML shredding fun is up next.

To get information out of the Automatic Seeding session…

Every time I have to work with XML I want to go to culinary school and become a tattooed cliche on Chopped. Upside? Brent might hire me to be his personal chef. Downside? I’d only be cooking for Ernie.

Here’s a sample of what we get back

I’ve moved the ‘less interesting’ columns off to the right.

Frenemy.
Frenemy.

These are my first clues that Jimmy is right about it being a backup and restore. One of the columns says “limit concurrent backups” and, we’re also sending file lists around. Particularly interesting is in the debug column from the hadr_ar_controller_debug item. Here’s pasted text from it.

Hey look, a restore

While I didn’t see an explicit backup command to match, we did pick up data like this:

A streaming backup! How cute. There’s more evidence in the Physical Seeding session, so let’s look there. Prerequisite XML horrors to follow.

And a sampling of data…

What an odd estimated completion date.
What an odd estimated completion date.

The old state and new state columns also point to backup and restore operations. I assume the completion date points to 1600 BECAUSE THIS IS ABSOLUTE WITCHCRAFT.

 

Ooh! Metrics!
Ooh! Metrics!

Ignore the smaller sizes at the bottom. I’ve clearly been doing this with a few different databases. The disk IO and network metrics are pretty awesome. Now I have to backtrack a little bit…

The SAP on SQL Server blog post talks about Trace Flag 9567 being used to enable compression. It says that it only has to be enabled on the Primary Replica to work, but even with it turned on on all three of my Replicas, the compression column says false. Perhaps, like parallel redo logs, it hasn’t been implemented yet. I tried both enabling it with DBCC TRACEON, and using it as a startup parameter. Which brings us to the next set of collectors…

Update from the comments: Luis and Robert point out that parallel redo is in RTM, just not documented yet

DMVs

These are also undocumented, and that kind of sucks. There are two that ‘match’ the XE sessions we have.

These can be joined around to other views to get back some alright information. I used these two queries. If you have anything better, feel free to let me know.

But we get sort of different information back in a couple places. This is part of what makes me wonder how fully formed this feature baby is. The completion estimate is in this century, heck, even this YEAR. The compression column is now a 0. Just a heads up, when I DIDN’T have Trace Flag 9567 on, that column was NULL. Turning it on changed it to 0. Heh. So uh, glad that’s… there.

I smell like tequila.
I smell like tequila.

Oh look, it’s the end

I know I said it before, but I love this new feature. There’s apparently still some stuff to work out, but it’s very promising so far. I’ll post updates as I get more information, but this is about the limit of what I can get without some official documentation.

Thanks for reading!

Previous Post
Availability Group Direct Seeding: How to fix a database that won’t sync
Next Post
Availability Group Direct Seeding: TDE’s Frenemy

16 Comments. Leave new

  • Joshua Tripp
    June 30, 2016 11:41 am

    This is incredible information! I’m going to have to read through a few times but I really feel this will evolve into such a dynamic feature. I really appreciate what you guys do.

    Reply
  • https://msdn.microsoft.com/en-us/library/aa915351

    1601-01-01- The day that Grace Murray Hopper and Bill Gates traveled back in time to and gave the code for FILETIME to Pope Gregory XIII…..

    Reply
  • Could you explain your comment, “Perhaps, like parallel redo logs, it hasn’t been implemented yet.” I know that parallel redo has been implemented. Is “parallel redo logs” something else?

    Reply
  • Luis Vargas (MSFT)
    June 30, 2016 4:35 pm

    Thanks for the post Erik. Good to see the interest on the feature.

    We’ve been documenting things lately: https://msdn.microsoft.com/en-us/library/mt735149.aspx

    We’ll look at the UI support (Wizard and Dashboard) later this year.

    Reply
    • Erik Darling
      July 1, 2016 8:38 am

      Ah, cool. That wasn’t around when I wrote this series of posts about the feature. Thanks.

      Reply
  • Andrej Kuklin
    July 1, 2016 4:45 am

    Concerning compression (TF 9567) (“It says that it only has to be enabled on the Primary Replica to work, but even with it turned on on all three of my Replicas, the compression column says false”)

    Sanjay Mishra from SQLCAT says that at least the DMV incorrectly reports that compression is not enabled (but the compression itself works). Maybe it’s the same small bug in the extended events.
    https://blogs.msdn.microsoft.com/sqlcat/2016/06/28/sqlsweet16-episode-2-availability-groups-automatic-seeding-2/

    Reply
    • Erik Darling
      July 1, 2016 8:40 am

      Could be! That post wasn’t published when I wrote this one, so, you know. Some of it will probably look dumb(er) in the future.

      Reply
  • Alexander Trepes
    January 24, 2019 11:49 am

    Hey Erik/Brent! I know this is a super old post but i use this query from Simon Liew (i’ve revised it a little to be more user friendly) to check the status of automatic seeding. When automatic seeding is executing this gives you the percentage complete and the estimated time left until the database is restored:

    (redacted)

    Reply
    • Alexander – thanks, but please don’t copy/paste other peoples’ work around the web. There can be some tricky copyright issues. Your best bet there is to contact the original authors, get the licensing for their code, and get their written permission to redistribute it. When you do get that permission, make sure you include clear licensing in your copy/pasted version too. Thanks for understanding!

      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.