SQL Server AlwaysOn Seeding – Database Not Automatically Seeding

availability-groupssql serversql-server-2017

I've added a database to an availability group that already has a few databases, all of which seeded and syncing fine to the secondary.

This last one gets added to the availability group, it would seem, but is marked as "not synchronizing" and no errors are returned. I've tried it multiple times with the same result.

I've looked in extended events, Event Viewer… nothing. Clicking on "warnings" in the dashboard says nothing useful. The database files do not exist on the secondary at all. I suspect that it was blocked right at the start of its seeding process (but clearly after the initial validations in the "add to AG" wizard).

I have this distinct feeling SQL server must be logging a relevant failure message to some DMV I'm not aware of.

Is there a canonical guide to ALL the places one is supposed to look for AlwaysOn related errors?

Best Answer

On the secondary replica, you should be able to see the status / outcome of seeding attempts in the sys.dm_hadr_automatic_seeding DMV. This includes columns like current_state, failure_state, and failure_state_desc, as well as timing related columns, that should give you a decent overview of seeding issues.

I have often seen seeding errors written to the SQL Server error log (also on the secondary replica), I don't think you mentioned looking there:

screenshot of SSMS error log

One thing I often forget to do is to run this on the secondary replica:

ALTER AVAILABILITY GROUP yourAgName GRANT CREATE ANY DATABASE;

Note: if you're interested in seeing which replicas have automatic seeding permissions, check out this blog post from Sean Gallardy: Automatic Seeding – Which Replicas Have Permission?

Once you've done that, you can attempt re-seeding by running this on the primary:

ALTER AVAILABILITY GROUP yourAgName
    MODIFY REPLICA ON 'SecondaryReplicaInstanceName' 
    WITH (SEEDING_MODE = AUTOMATIC);

That normally gets me up and running.

As far as this question:

Is there a canonical guide to ALL the places one is supposed to look for AlwaysOn related errors?

There is a great blog post from the SQL Server team here: Failover Detection Utility – Availability Group Failover Analysis Made Easy

It's about troubleshooting failovers specifically, but it lists out the main places where you'll see AG errors and diagnostic information:

  • SQL error logs
  • Always On Availability Groups Extended Event Logs
  • System Health Extended Event Logs
  • System log
  • Windows cluster log
Related Question