While deploying Always On Availability Group with a client, they found that when they use automatic seeding, the database was not shown on secondary. After digging more, we found that it was there earlier in “Restoring” state when seeding was in progress but then it automatically disappeared from the list of databases on secondary. Here is the option in the wizard.
Here are a few earlier blogs which I wrote about seeding failures.
I checked SQL Server ERRORLOG to figure out what might have gone wrong with auto-seeding.
The important messages about the database, which we tried to seed, are as follows.
Error: 1412, Severity: 16, State: 211.
The remote copy of database “DB” has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
Starting up database ‘DB’.
The database ‘DB’ is marked RESTORING and is in a state that does not allow recovery to be run.
Automatic seeding of availability database ‘DB’ in availability group ‘AG’ failed with an unrecoverable error. Correct the problem, then issue an ALTER AVAILABILITY GROUP command to set SEEDING_MODE = AUTOMATIC on the replica to restart seeding.
The last bullet has an interesting message and tells who to restart the seeding. The very first message (Error: 1412, Severity: 16, State: 211.) tells us the cause of the problem.
You need to monitor seeding using DMVs. There are two dynamic management views (DMVs) for monitoring seeding: sys.dm_hadr_automatic_seeding and sys.dm_hadr_physical_seeding_stats. One of them shows information about seedings happening currently and “stats” show the historical data about seeding. In this case, seeding failed with SQL Error.
The cause of the problem was an additional log backup which happened while seeding was in progress. I used my own earlier blog to find backup history SQL SERVER – Get Database Backup History for a Single Database
After running the script from my blog (on ALL replicas), we found that a backup job which was taking log backup on “another” replica every 15 minutes. We disabled the job and started seeding again. This time it worked without any issue.
Have you encountered some error during Always On Availability Group? Please share via comments and help others.
Reference: Pinal Dave (https://blog.SQLAuthority.com)