4

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

1 Answer 1

5

After a forced failover testing with async mode, do I need to rebuild my old primary database?

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:

After a forced failover, all secondary databases are suspended. This includes the former primary databases, after the former primary replica comes back online and discovers that it is now a secondary replica. You must manually resume each suspended database individually on each secondary replica.

When a secondary database is resumed, it initiates data synchronization with the corresponding primary database. The secondary database rolls back any log records that were never committed on the new primary database. Therefore, if you are concerned about possible data loss on the post-failover primary databases, you should attempt to create a database snapshot on the suspended databases on one of the synchronous-commit secondary databases.

Please see this BOL reference on how to resume an AG database.

The T-SQL for this would be:

alter database YourDatabaseName
set hadr resume;

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.

5
  • Hi Thomas Thank you for the response. Please can you break this one step further for me. This is a test and there are no txns since the failover. When it shows as not synchronized. What do I need to do to resume the database.Do I need to delete the availability group? I do not see the suspended state... Commented Mar 20, 2014 at 14:58
  • For the availability group I am only able to remove the active primary db not the secondary - remove gets greyed out. do I delete the availability group resume the db and then recreate the availability group? Commented Mar 20, 2014 at 15:07
  • @TDPSQL_Newbie See my edit. And definitely follow the "note/warning/disclaimer". Commented Mar 20, 2014 at 15:23
  • Many thanks Thomas, used this link. Appreciate your input. Commented Mar 20, 2014 at 15:51
  • @TDPSQL_Newbie No problem! Always glad to help. Commented Mar 20, 2014 at 15:53

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.