Morning All,
We have an issue with our secondary replica server in a Basic Availability group in that it stopped synchronising a while ago. There are three separate groups, each with one database (as there are BAGs). Each of the databases is in a similar state.
As the primary server can't truncate the logs when it takes a backup because the secondary is not up-to-date, the log drives keep filling up and the databases / backups are getting large to.
We tried rebooting the secondary as we understand that should re-initiate a sync but get the following error:
The remote copy of database <db name> has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
Therefore, we are presuming that the secondary is so far out of date that the primary doesn't have the necessary log information to play into the secondary.
What's the best way to remedy this situation? We are presuming that we need to restore a fresh copy of the data but not sure the best way to do this with minimal downtime (the application does handle outages fairly well as it has a functional local copy of the data that it uses).
This article sort-of covers it but not quite our situation (it presumes that the database info is still available on the secondary) but we can't find any other articles that explain the necessary steps:
https://www.sqlpassion.at/archive/2017/09/25/adding-a-replica-back-into-a-sql-server-availability-group/
Would the best approach be to:
- Remove the database from the availability group on the secondary (not sure if this is possible) and then add back in / reseed
- Remove the database from the AG on the primary, copy data to the secondary and then add back in
- Just restore a recent backup of the database / logs from the primary to the secondary while still in AG (not sure if this is possible if it's still in the AG)
- Remove the replica server from the AG together, delete the database and then re-seed from Primary when adding back into AG.
Grateful for any advice!
Steve