Availability Group Direct Seeding: Mind Your Backup Jobs

I’ll get right to the point

While you’re Direct Seeding, you have to be careful with any other full or differential backup jobs running on the server. This is an artifact of the Direct Seeding process, but it’s one you should be aware of up front.

In the screencap below, courtesy of sp_whoisactive there’s a differential backup waiting on LCK_M_U, being blocked by session 89, running NULL. That NULL is the backup/restore process that Direct Seeding is going through. I can tell because it’s waiting on ASYNC_IO_COMPLETION, which is commonly associated with backups writing to disk.

Sup.

Sup.

Behind the scenes

One of the steps that you can see Direct Seeding go through if you watch it via Extended Events is LIMIT_CONCURRENT_BACKUPS. This isn’t just for Direct Seeding — meaning you can only get Direct Seeding up and running one database at a time — this apparently goes for other backups of the database you’re currently synchronizing.

Reading is for DWEEBS

Reading is for DWEEBS

Who has to be careful?

Basically everyone. Well, anyone whose backup jobs automatically detect new databases. If you’re using Maintenance Plans, you might have finally won a round, here. Whether it’s Ola, Minion, or Dell LiteSpeed, chances are you don’t have one backup job per database, because that would be crazy for anything other than log backups. Your backup jobs likely gather a list of databases, and cycle through them doing ~the necessary~

If your full or differential jobs hit a database while Direct Seeding is doing the initial sync, they’ll be blocked until it finishes. This can really mess with RPO and RTO for your other databases, especially if it takes a while to sync, or you’re syncing a bunch of databases with Direct Seeding, and the backup job keeps getting stuck behind each database synchronization.

Thanks for reading!

, , ,
Previous Post
The Top 10 Feature Requests for SQL Server on Connect.Microsoft.com
Next Post
Last Day Shenanigans

7 Comments. Leave new

  • Thank you Erik for the post. It is one more thing about Always On to scare the fire out of me. There is not enough of me to do an AO in my environment. I think I need to hash out Always On in a lab so when the time comes.

    • Erik Darling
      July 7, 2016 12:13 pm

      I agree! They’re terrifying. I mean, they’re great when they’re working, though.

  • Oof.

  • Is it any way to kill direct seedning proces ?

    kill 259

    Msg 6107, Level 14, State 1, Server SRV, Line 1
    Only user processes can be killed.

    • Erik Darling
      March 7, 2017 7:31 am

      You know, back when I wrote this, Direct Seeding found so many ways to kill itself that I never looked into how to do it manually. Off the top of my head — have you tried altering the AG to disable it?

  • I’ve tried:

    1. remove db from AG, but backup/restore was not stopped
    2. kill backup/restore process with DAC, no success
    3. restart secondary – that works perfect 🙂

    I will try your suggestion, thanks.

Menu
{"cart_token":"","hash":"","cart_data":""}