0

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?

Improve this question
3

2 Answers 2

Reset to default
1

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:

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.

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.

Reference:

• Differences between availability modes for an Always On availability group

• Failover and Failover Modes (Always On Availability Groups)

• How It Works: Always On–When Is My Secondary Failover Ready?

• Perform a Forced Manual Failover of an Always On Availability Group (SQL Server)

Improve this answer
1

This is not possible since database is added to AOAG You cannot take the db offline unless you remove it from AG

You can see and verify this yourself simply by adding some "TestDB" to your AG and try to take if offline

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

Yes you have to wait for sync to complete in any scenario, if you do not want to lose any data

Improve this answer
2
  • So it technically is possible (without removing it from the AG) if you turn off the SQL Server service (which is what I did), but I'm just wondering what's going to happen when I turn the service back online?
    – J.D.
    Commented Feb 9, 2020 at 7:08
  • @J.D. if you turn off SQL Server service, all databases go offline... and they go online when you turn service back on
    – Aleksey Vitsko
    Commented Feb 9, 2020 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.