Transactional replication of SQL SERVER and ALWAYS ON data synchronization method

Question

Hello, I am a junior DBA running SQL Server. Recently, while considering ways to synchronize data, I became curious about transactional replication and ALWAYS ON.

In the case of transactional replication, when a data change occurs in the source DB, the data is synchronized to the target DB through a replication agent using a replication SP. How does ALWAYS ON synchronize data to multi-DBMS? I'm curious about how the internal architecture works. Also, is my understanding of how transactional replication works?

Answer 1

Hi  @고민재,

Always On requires doing three tasks to synchronization the data:

1. Record the data changes that occur on the primary replica.
2. Transfer the records to each secondary replica.
3. Complete the data changes on the secondary replica.

The three tasks are mainly completed by the following four threads:

1. Log Writer: It is responsible for recording the modified log information into a log buffer in memory and then written into the physical log file.
2. Log Scanner: It is responsible for packing log records from the log buffer or log files into log blocks and then send them to each secondary replica.
3. Harden: It will write the log blocks from Log Scanner into log files in the disk of secondary replica.
4. Redo: It is responsible for reading the log blocks from disk and translating into data modification operations.

When the redo thread completes its work, the database on the secondary replica will be the same as the primary.

In addition, the data synchronous of Always On can be divided into asynchronous commit and synchronous commit. Hope this article can help you know the difference of them. Availabilitymode for availability groups - SQL Server Always On | Microsoft Learn

Finally, hope this article can help you understand how transaction replication works. TransactionalReplication - SQL Server | Microsoft Learn

 

Best regards

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.