Sql-server – If I manually failover the AlwaysOn AG primary replica to the secondary and take the primary database offline will it auto-sync when back online

availability-groupsfailoverhigh-availabilitysql serversql-server-2016

I have an AlwaysOn AG setup between Database A (primary replica) on Server 1 and Database B (secondary replica) on Server 2.

If I force a manual failover from my primary replica on Server 1 to the secondary replica on Server 2 and then take Database A (on Server 1) offline, will it automatically sync back up from Database B (on Server 2) when I bring it back online?

Do I have to wait for the sync to finish before manually failing back over to Database A from Database B?

Can I track it's sync progress in the AG Dashboard?

Note: I took Database A offline without removing it from the AG by stopping the SQL Server service. So I'm wondering what will happen when I start the service again?

Best Answer

What happens when the old primary is available and back online?

Synchronous-commit mode and Automatic Failover:

When the former primary replica comes back online, it takes on the secondary role, and the former primary database becomes the secondary database. The new secondary replica quickly resynchronizes the new secondary databases with the corresponding primary databases.

Synchronous-commit mode Planned manual failover:

Asynchronous-commit mode Forced failover:

It transitions to the secondary role, causing the former primary databases to become secondary databases and transition into the SUSPENDED state. It gives you a chance to recover data before resuming synchronization.

Important

Transaction log truncation is delayed on a primary database while any of its secondary databases are suspended (not available to sync). Also, the synchronization health of a synchronous-commit secondary replica cannot transition to HEALTHY as long as any local database remains suspended.