How does an async AG secondary sync itself with the primary when secondary missed tlog record due to a power failure on secondary server?

All we need is an easy explanation of the problem, so here it is.

In a SQL AG (read-scale) async mode, which doesn’t require a WSFC cluster, suppose the following scenario:

  1. Primary writes the data into its transaction log and sends this log record to the secondary
  2. Suppose the Secondary gets powered off before the above log record reaches the secondary

When the secondary starts up, then how does it sync its database with the primary? Is this auto or manual process?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

When the secondary starts up, then how does it sync its database with the primary?

I’m making broad strokes here because the minutia isn’t important, the overall process, however, is the main point of understanding. If you’d like to see how this internally works, there are XEs and various other methods which can be used to deduce the deep internals.

When the secondary starts up and connects to the primary, it’ll exchange some basic information. One of these items is where the secondary is at in regard to the log which will be negotiated with primary. Various options could then occur, such as:

  1. The secondary is past the primary and needs to undo back to the common point.
  2. The secondary is at the same location as the primary and any new log will be sent.
  3. The secondary is behind the primary. This has different sub options, such as being too far behind for a common point and needing to be rolled up to a common fork, or a common fork exists, the secondary can have the log stream begin at the common fork and continue until caught up.

Most of this is automatic, the only part that isn’t is if there is no common fork for the primary and secondary and manual intervention will need to take place. This will result in an error stating the database is too far behind, which is self-explanatory.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply