How data is replicated from the Primary Replica to the Secondary Replica in a SQL Server Always On Availability Group

When it comes to high availability in SQL Server, Always On Availability Groups offer a robust solution for ensuring data resilience and fault tolerance. At the heart of this technology is a sophisticated log-based replication mechanism distinct from traditional database mirroring or snapshot replication. Let’s walk through exactly how data moves from the Primary Replica to the Secondary Replica, step by step.

High-Level Process Flow 

Primary Replica → Log Generation → Log Transport → Log Reception → Redo → Acknowledgement.

Step-by-Step Explanation

1. Client Writes Data to Primary Replica

* A client application sends a transaction (e.g., INSERT, UPDATE) to the Primary Replica.

* SQL Server writes this transaction to the transaction log of the primary database.

* This log write is made to the log buffer and flushed to disk before the transaction is considered committed.

2. Log Block is Captured by the Always On Transport Manager

* The HADR_LOG_SEND_MANAGER process monitors the transaction log for changes.

* It captures committed log records in log blocks (typically 60KB) and places them in a send queue (log_send_queue_size).

* The log blocks are then sent asynchronously or synchronously based on the Availability Mode:

    ** Synchronous Commit: Primary waits for acknowledgment from secondary before confirming commit to client.

    ** Asynchronous Commit: Primary does not wait; faster, but risk of data loss if failover occurs.

3. Log Blocks Are Sent Over the Network

* The log blocks are shipped over the wire (TCP/IP) from the primary replica to the secondary replica(s).

* This is handled by SQL Server’s internal Availability Group transport layer, not file-based log shipping.

* Latency here depends on network performance. High log_send_queue_size may indicate network issues.

4. Log Blocks Are Received on Secondary

* The secondary replica receives the log blocks and places them in the log cache or redo queue.

* This is tracked via:

    ** log_send_rate (from primary side)

    ** redo_queue_size (on secondary)

5. Redo Process on Secondary

* The HADR_REDO_MANAGER on the secondary reads the log blocks and replays the changes to the database in the exact order.

* This process is similar to recovery after crash: log-based redo.

* Secondary databases are always in restoring mode, which allows continuous replay but not direct write access (read-only access only in readable secondaries).

6. Acknowledgment (Synchronous Mode Only)

* In Synchronous Commit mode, once the log block is hardened to disk on the secondary, it sends an acknowledgment back to the primary.

* Only after receiving this ACK, the primary considers the transaction committed and returns success to the client.

                                                        SQL Server Always On data replication process