May I request your input.
I setup SQL 2012 with always on availability group for my databases in sync and secondary readable mode. I completed failover tests with sync mode and it is functioning as expected.
After a forced failover testing with async mode, do I need to rebuild my old primary database? Because changes in the new primary are not reflected back. The database status is not synchronized…instead of synchronizing…
Appreciate your input as I get my head around this concept.
Thank you
Best Answer
I'm not sure exactly what you mean by "rebuild" the database, but provided the databases are still in a working condition then you shouldn't need to take any actions like that.
What you're seeing, by performing a forced failover, is by design. If you do a forced failover, you could potentially have failed over to a replica that isn't completely caught up or at the same point-in-time as the primary replica. Because of that, data movement is suspended to the secondary replica(s) from the now primary replica so there is a way to have manual intervention if you are now on a database that is "behind". That behavior that you are seeing is a good thing.
This BOL reference explains it all:
Please see this BOL reference on how to resume an AG database.
The T-SQL for this would be:
NOTE/WARNING/DISCLAIMER: You really need to do some leg work to ensure that you are not causing data loss by resuming data movement. See above, it could be a huge problem. The point of suspending data movement is for this very reason: To manually make sure you can recover as much data as possible. When you resume data movement, that could be irreversible. When you resume data movement after a forced failover, you have to have the words "potential data loss" in the forefront of your mind always.