Adding database back to Always On High Availability Group

            

Summary: If databases in secondary replica are out of sync with primary then below is the workaround which helps to fix.

    At very first take the database out of Always on availability group on primary replica by right click on the database you want to remove from always on and click on Remove Database from Always on group.

AlwaysOn High Availability-> Availability Groups->AGGroupName (Primary)->Availability Databases

    Taking full backup of primary database is the prerequisite. Also the database recovery model should be Full.

    When you right click on Availability Databases and select Add Database the SQL server will give all databases list and validated results against each DB. Please refer below screens..observe different statuses against each database.

4   Next step is Select Initial Data Synchronization. Full is when you want to add the database for the first time or reconfigure it from scratch. I have used this option to avoid manual backups and restores. We have to create one share folder and give access to help SQL server to take backups from primary replica and use it to restore in secondary replica.

Refer below link to know the synchronization options.

https://msdn.microsoft.com/en-us/library/hh231021.aspx

Received below error as the share location I gave is not valid network file location. 

   Next step is connecting secondary replicas. I have only one replica which showing in the below screen. Click connect which helps to connect to the replica.

Next is validation, validation details are explained and results against each validation. I didn’t delete the database in secondary replica (already configured Always on before in this environment) so I have received below error message so make sure you delete it in secondary replica before setup with Full synchronization.

Next option is disabled until all validations are successful. See below screen.

  

     When you click next and expand for more details it will show the steps of backups restores (with completion percentage ) and joining the database to the secondary replica.

It is done now.

Make sure to check the dashboard and see the synchronization statuses on secondary replica. Delete the backups of databases created temporarily in network folder to save the disk space.