SQL Server Availability Groups,Seeding Failures and How To Re-Trigger Seeding

I love AAG! In fact its the one feature I always push when recommending an upgrade to existing SQL Server Infrastructure.

I could write article after article about how great they are and some pitfalls not to fall into, in fact I may just do that but for now lets talk about when they aren't so great.

One of the biggest pains I often come across is when Seeding just does not work, you've checked your drives are the same, you've checked that access is there, you've even checked the database doesn't exist and the physical file locations are clean of copies but it STILL doesn't work. Your AG Dashboard is about as useful as a chocolate fireguard and the logs really don't hint at anything

Well hopefully the below queries will help you out!

First off - lets check the secondary's themselves, this little beauty will get you information around databases that the Secondary Replica you are running on knows about. Often I find the key information is not whats in the table but actually whats missing

SELECT 
    Seed.start_time
    ,Seed.completion_time
    ,ag.name
    ,Clust.database_name
    ,Seed.current_state
    ,Seed.performed_seeding
    ,Seed.failure_state
    ,Seed.failure_state_desc


FROM sys.dm_hadr_automatic_seeding as Seed 
JOIN sys.availability_databases_cluster as Clust
    ON Seed.ag_db_id = Clust.group_database_id
JOIN sys.availability_groups as ag 
    ON Seed.ag_id = ag.group_id

Second up this useful query, if you run this on the primary you can start to identify problems going on!

SELECT AR.replica_server_name AS ReplikaServer
     , AG.[name] AS AvailabilityGroup, DB.[name] AS DbName
     , AutoSeed.current_state
     , AutoSeed.start_time
     , AutoSeed.failure_state
     , AutoSeed.failure_state_desc
     , AutoSeed.error_code
     , AutoSeed.number_of_attempts
FROM sys.dm_hadr_automatic_seeding AS AutoSeed
  INNER JOIN sys.availability_groups AS AG on AG.group_id = AutoSeed.ag_id
  INNER JOIN sys.databases AS DB ON AutoSeed.ag_db_id = DB.group_database_id
  INNER JOIN sys.availability_replicas AS AR ON AutoSeed.ag_remote_replica_id = AR.replica_id
ORDER BY AutoSeed.start_time DESC;

And finally this query, if you find you get Timeouts like below you can attempt to re-trigger seeding by running the below code. Naturally your Secondary's need to have the below enabled before you even attempt to add a database to an AAG but it also works to re-trigger the process.

ALTER AVAILABILITY GROUP [ag_Name] GRANT CREATE ANY DATABASE;

Once you've run this fire off the above two query's again and you should now hopefully see seeding attempts starting to run, when this are done they will switch to Completed!

Any questions feel free to give me a shout!

Kurt