3 node AOAG 1 out of Sync

  • Super Cat

    SSCertifiable

    Points: 7316

    One node has fallen out of sync, as far as I am aware I will need to drop the databases out of the AG group Then resync both replicas.

    Is there a way I can just resync the node that has fallen behind. Resume Movement is not an option as it is too far behind.

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    Are there any other databases in the AG? If not you should be able to remove the replica and add it back in again without having to take the database out of the AG on the other two nodes.

  • Super Cat

    SSCertifiable

    Points: 7316

    Yes about 10.

  • Super Cat

    SSCertifiable

    Points: 7316

    All dbs are out of SYNC is this the best way to do it. Rather than remove the dbs from the AG and resync all replicas.

  • Perry Whittle

    SSC Guru

    Points: 233859

    Talib123 - Tuesday, March 28, 2017 1:37 AM

    One node has fallen out of sync, as far as I am aware I will need to drop the databases out of the AG group Then resync both replicas.

    Is there a way I can just resync the node that has fallen behind. Resume Movement is not an option as it is too far behind.

    remove the secondary databases from the AG.
    Restore the secondary databases and get them in sync using log\diff backups then re join to the AG

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Super Cat

    SSCertifiable

    Points: 7316

    Perry Can that be done on a per replica basis.

  • Super Cat

    SSCertifiable

    Points: 7316

    There are 3 nodes involved here, 

    1 Priamry

    2 Secondaries

    Only one of the secondaries is out of sync.

    Removing the Secondarires from the AG would need to done on the Primary?

    This would remove it from the Secondary that is working fine too?
    Which is what I'm trying to avoid.

  • Tiger10

    Grasshopper

    Points: 12

    Yes this can be done on a per replica basis. Also you can do this from the secondary replica that is not in sync. 

    On the secondary replica, expand the AG and look at the availability databases, right click the database that's not in sync and remove the secondary database.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Talib123 - Tuesday, March 28, 2017 3:43 AM

    All dbs are out of SYNC is this the best way to do it. Rather than remove the dbs from the AG and resync all replicas.

    If only one node is out of sync, how far out of sync is it?

    What patch level are you on (sql edition, patch and cu please)?

    Is it a readable secondary?

    Is your log file growing on the primary?

    Before taking action, these are important questions to answer. If the log file is growing for instance you may need to go down the path that Perry has suggested. If not, this may be related to a CU that was recently released.

    It could also be a stuck thread that gets released and allows the secondary to immediately resync just by bouncing the SQL Service on that instance.

    Also, it does happen every once in a while that you just need to right click the database on the secondary and select the option to start sync'ing. Applying windows patches will occasionally pause the sync.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Super Cat

    SSCertifiable

    Points: 7316

    Thanks for the replies. Most helpful. The server came back online and AOAG caught up.

    I was going to ask how it did that when it was down for a couple of days. This line might explain it.

    It could also be a stuck thread that gets released and allows the secondary to immediately resync just by bouncing the SQL Service on that instance.

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    Do do as SQLRNNR suggests and look into patching to 2012 SP3 CU7 if you haven't already. The patch was only released in January this year.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply