Differences Between CDC, Trigger, and Transaction Log Replication

Question

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.

Answer 1

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)?

TRIGGER

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,

Aniya