2

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?

1 Answer 1

8

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.

6
  • Can you give me an exmaple of The secondary is past the primary?
    – variable
    Dec 22, 2022 at 18:12
  • @variable a transaction is committed, the local primary takes a long time to write to disk, the secondary writes it to disk immediately, the transaction waits on the primary. Dec 22, 2022 at 20:19
  • When a transaction is committed on local primary, then, surely it is hardened on the disk (log) and only then do we say it is committed. No?
    – variable
    Dec 23, 2022 at 2:41
  • Correct, it won't be acknowledged until it's hardened, however at that point the secondary is technically ahead of the primary. Dec 23, 2022 at 11:44
  • Ok so you are saying that (in sync or async) the log record goes from primary to the secondary without it being hardened on the primary, yes?
    – variable
    Dec 23, 2022 at 12:20

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.