Fix a broken secondary on a SQL availability group

Question

I'm having trouble finding the proper steps for what I would think would be a pretty routine task:

My SQL availability group secondary is no longer synching, and I need to get it working again without breaking my application.

(It has been broken for a while, so presumably this would be via a fresh backup of the primary restored to the secondary. This is SQL 2017, if that matters. All the other pre-reqs are already in place, since we've had this working before.)

All the references I can find either assume I'm starting completely from scratch (I already have an availability group listener with live applications using it) or are for unrelated tasks like restoring over the primary.

Other pages I've found say you have to remove then re-add the database to the availability group to get things re-initialized. I can do that if it's the only way, but that's definitely not preferred, since my applications are currently using the listener name.

What are the proper steps to get my secondary working properly again?

Answer 1

Removing a secondary database from an availability group doesn't impact the primary's membership, and therefore won't impact applications connecting to the listener.

So, combining the relevant portions from these Microsoft documents, we have the following steps to fix a broken secondary:

1. On the secondary server, remove the secondary database from the availability group (it may already be removed, in which case this step will do nothing):

   ALTER DATABASE myDatabase SET HADR OFF;

2. On the primary server, perform a full and transaction log backup of the primary database.

3. If needed, disable scheduled transaction log backups on the primary so they don't run again before you complete the remaining steps.

4. Restore the full and tran log backups to the secondary server, with option NORECOVERY.

5. Join the secondary back to the AG group:

   ALTER DATABASE myDatabase SET HADR AVAILABILITY GROUP = myGroup;

6. Check the AG dashboard and make sure it shows proper synchronization with the secondary.

7. Re-enable the tran log backup schedule on the primary server, if you disabled it.