After poking around in SSMS for a while I noticed that on the secondary replica there was a pause icon next to the Availability Databases. The primary had shown both were "green", but there was an option on the secondary to Resume Data Movement. I resumed the first database, and immediately the In Recovery status message was removed. A minute later it changed from Not Synchronizing to Synchronized, and everything worked as expected.
Here is a screenshot of the AG Databases after I fixed "Patch", but before fixing the test database:
Note you can also use TSQL on the secondary to resume replication on multiple database at the same time:
ALTER DATABASE [Patch] SET HADR RESUME;
ALTER DATABASE [test] SET HADR RESUME;
GO
Can someone please confirm if this is correct?
Yes, you are 100% correct!
Is there any way we can mitigate this, ie. ensuring there's no index maintenance during this window or switching to aynschronous commit mode?
The mitigation is not with the mode that the replicas run it, it's removing that replica from the AG.
Does this pertain to my situation as described above and if so does this mean that the potential for the transaction to fill is mitigated by design?
Yes, that's what will happen if you change nothing. No, the transaction log will still fill up. Regardless of synchronous or asynchronous modes the primary must keep all log blocks that are required for the replica that is the furthest behind.
In your scenario, there are only two replicas a primary and a secondary. The secondary will be offline for a while. You've already correctly stated what will happen, the send queue will get larger as the replica is offline and stays that way. This happens no matter the mode.
The mitigation is to remove the replica that will be down for the extended period of time. This allows the primary to properly truncate the log with proper transaction log backups.
Once the secondary is back up, since it was kicked out of the AG all of the databases participating in that AG will be in a restoring state. To get back to a good steady state and bring the replica back into the AG, take the log backups that were run during the period the secondary replica was offline and apply them to the databases, leaving them in a restoring state (with no recovery). Once you get close, pause all transaction log backups on the primary and join the replica back into the AG. Resume the log backups on the primary.
Best Answer
Yes. Use SQL 2014 or later :)
Active Secondaries: Readable Secondary Replicas (Always On Availability Groups)