DB out of sync secondary node HAG

Question

I have a question about HAG / DB synchronization in MS SQL server.

I have a HAG and in the secondary node I had to remove one DB on Saturday from the HAG. I added it back on Sunday and it's been out of sync since then. This secondary node it's read-only and more like a backup so it's not impacting anything.

I want to know what would be the best approach to put this DB back in sync. Right now the DB it's in the available databases but not synchronizing.

If I join this database to the HAG, it will automatically start seeding from the primary or is it better to restore the last full backup and t-logs and then join to the HAG? The DB it's quite large. I tried restoring it in another VM and it took approximately two days and a half so I guess that I should stop the full backup for that DB in specific and just restore the last full backup and all t-logs until up to date right?

Thanks!

EDIT: I ended up restoring the full last backup with the t-logs in norecovery in the secondary node and then just joining the DB into the AG. Worked like a charm.

Thanks for the responses!

Answer 1

Imho, it's worth to try the following: Simulate the same scenario but with a tiny test db. Don't forget to do full/log backups which happened (?) with your big db on primary since Sunday. It will take ~10-15 mins to reproduce and you will know for sure will it go or not.

Regarding the question: I believe it will not start auto-seeding because your DB was recovered and readable before you removed it from HAG. Also, if there was a full backup on primary, it also affects the log chain.

Answer 2

I have a HAG

I'm assuming that you have a SQL Server Availability Group. The common abbreviation is just "AG" just so you know.

I want to know what would be the best approach to put this DB back in sync.

You need to determine exactly why your database is not synchronizing. One first step is just trying to resume data movement, by connecting to the secondary and running this:

ALTER DATABASE YourDatabaseName SET HADR RESUME;

If that works, you should see the database change from "NOT SYNCHRONIZING" to "SYNCHRONIZING." Eventually it will catch up and be "SYNCHRONIZED."

If the secondary database got off track somehow, to where it can't be synchronized (if the database or tables were altered in any way while it was removed), then you'll need to re-initialize the secondary.

You mentioned backup and restore, which is the traditional option.

If you're on SQL Server 2016 or higher, you could also consider using "automatic seeding" to recreate the secondary database. This could be a big time (and disk space) saver compared to the traditional backup / restore option. You'll also need to meet the other requirements outlined in the documentation, like the secondary database having the exact same disk names and file layouts.

There was a time when I would have thought that 4 TB was far too large of a database to use automatic seeding. However, after reading Taryn Pratt's account of seeding a 40 TB database (see her blog here under "Time to Try an Availability Group").