SQL Server：DB-A1表状态变更时更新DB-B1表的非触发器方案

Alright, since you can't create triggers on the external DB-A1 (totally get the restrictions here), here are several practical, non-intrusive ways to sync those Status field changes from T1 over to the specified columns in T2 (DB-B1):

1. Scheduled Polling (The Quick & Dirty Option) #

This is the easiest to implement without any special permissions or dependencies. The idea is to regularly check DB-A1's T1 table for changes and sync them over.

• How to pull it off:
  • Keep track of your last sync timestamp (or the max ID from T1 if you have an auto-incrementing key). Each time you run the sync, query T1 for records where Status has changed since your last check, or where the LastModified timestamp is newer than your last sync time. Something like:

    SELECT ID, Status, LastModified 
    FROM DB-A1.dbo.T1 
    WHERE LastModified > @LastSyncTimestamp 
      OR (Status != @PreviousStatusForID AND ID > @LastSyncedMaxID)
    

  • Use a task scheduler to run this sync script on a schedule—think Windows Task Scheduler, Linux cron, or your database's built-in job system (like SQL Server Agent or MySQL Event Scheduler).
  • In the script, take the changed records and run an UPDATE on DB-B1's T2 to set the target column with the new Status.
• Pros & Cons:
  • ✅ Zero impact on DB-A1, dead simple to set up
  • ❌ Has inherent latency (depends on how often you schedule the job), and large tables might see minor performance hits from frequent queries

2. Change Data Capture (CDC) - Low-Latency, Low-Overhead #

If the owners of DB-A1 are willing to help out a bit, CDC is a great option. Most modern databases (SQL Server, MySQL 8.0+, PostgreSQL) support CDC, which captures changes to tables without triggers.

• How to pull it off:
  • Ask the DB-A1 admin to enable CDC for the T1 table. This creates system-level logs that track all changes to the table.
  • Use an ETL tool (like SSIS, Apache NiFi) or a custom script to read the CDC logs, filter for only the Status field changes, and sync those to T2 in DB-B1.
• Pros & Cons:
  • ✅ Low latency, only captures changed data (minimal performance hit), reliable
  • ❌ Requires cooperation from the DB-A1 team, and CDC setup varies across database systems

3. Parse Database Transaction Logs #

For near-real-time sync, you can directly read the source database's transaction logs (like SQL Server's transaction log or MySQL's binlog) to extract Status changes from T1.

• How to pull it off:
  • Make sure DB-A1 is configured to retain transaction logs (e.g., SQL Server in full recovery mode, MySQL with binlog enabled—again, might need admin help here).
  • Use a log parsing tool or write a custom program to dig through the logs, find updates to T1's Status column, and push those changes to T2.
• Pros & Cons:
  • ✅ Near-real-time sync, no impact on DB-A1's regular operations
  • ❌ Needs log access permissions, log parsing can be complex, and log formats differ between databases

4. Application-Level Sync #

If all updates to T1's Status happen through a specific application, you can add sync logic directly in that app.

• How to pull it off:
  • Find the code that updates T1's Status—after the update is successfully committed, add a step to update the corresponding record in T2 (DB-B1).
  • To avoid data inconsistencies (like T1 updating but T2 failing), use a distributed transaction or a message queue (e.g., Kafka, RabbitMQ) to queue the sync task. That way, if the initial sync fails, it can retry later.
• Pros & Cons:
  • ✅ Real-time sync, can enforce data consistency if implemented properly
  • ❌ Only works if all Status updates go through that app—any direct database edits to T1 will slip through the cracks

5. Use a Dedicated Data Sync Tool #

If you don't want to build custom code, use a pre-built ETL or data sync tool. Tools like Debezium (open-source, CDC-based), Talend, or Azure Data Factory can handle the sync automatically.

• How to pull it off:
  • Configure the tool to connect to both DB-A1 and DB-B1.
  • Set up a sync rule that only monitors changes to T1's Status column and maps them to the target column in T2.
  • Let the tool handle the rest—most include built-in retry, error handling, and monitoring.
• Pros & Cons:
  • ✅ No custom code needed, robust out-of-the-box features
  • ❌ Might require a learning curve to configure, some tools are paid

内容的提问来源于stack exchange，提问作者Jonny Avenger