Differences Between CDC, Trigger, and Transaction Log Replication

고민재 0 Reputation points

Differences Between CDC, Trigger, and Transaction Log Replication

Hello. While operating SQL SERVER, I'm currently researching CDC, Trigger, and Transaction Log Replication for data synchronization. All three methods are used for data synchronization, but I believe there are differences in when they detect data changes, which could help reduce operational risks.

I understand that CDC detects changes by monitoring changes in the transaction log and storing data in separate tables, not in the original tables. How do Trigger and Transaction Log Replication detect data changes? AND After detection, data synchronization in triggers involves invoking special stored procedures, and in replication, it involves running replication SP's JOB agents. Is this information correct?

Thank you.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,001 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. AniyaTang-MSFT 9,681 Reputation points Microsoft Vendor

    Hi @고민재

    A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.

    Transactional replication typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time).

    For more details, you can refer to these official documents:

    What is change data capture (CDC)?


    Transactional Replication

    If the answer is helpful, please click Accept Answer and Up-Vote for the same. If you have any questions, please feel free to let me know.

    Best regards,