SQL Server Issue and Solution
Error Summary:
To Fix SQL Server Always on Availability Group Database in Secondary Replica is Not Synchronized with Primary Replica.
REASON:
If there is an issue with the Database in Always On Availability Group (AOAG) was not synchronizing the Secondary Replica even though it was using ‘Synchronous commit’ Availability Mode for the secondary Replica, then process of troubleshooting this issue is as follows:
RESOLUTION:
1.First, we check with the AOAG Dashboard in SSMS to check the AOAG Health.
If we find that the Database is ‘not synchronizing’ on the Secondary Replica and shown Warnings. As this database is healthy on the Primary Replica.
2.Click on ‘Warnings’ to show the details of the warnings, if we find that the ‘secondary Database is not joined.’
3.We should rejoin the database on the Secondary Replica to resolve this issue by following these Steps:
Step 1: Remove the Database from Always On Availability Group
Go to the Availability databases, right click on the Database and remove from Availability Group.
Step 2: Restore the Database on the Primary replica with the latest Full Backup and latest Transaction Log Backup with Recovery.
Step 3: Restore the Database on the Secondary Replica with this latest Full Backup and latest Transaction Log Backup with no recovery or in Restoring mode in Always On Availability Group.
Step 4: Go to the Primary Replica and add that particular Database that we have restored, back to the Always On Availability Group.
Right click on Availability Databases–>Add Database–>Select the Database–>Click Next
Step 5: Use ‘Join Only’ option to join the Database by connecting to the Secondary Replica, Click Next and Click Finish. Now we can see that the Database has joined Successfully.
Hence, the Database will be synchronized on the Secondary Replica successfully.
