Synchronizing Data Between Two Databases

Two databases hold the same customer row, and each claims to be current. Synchronizing data between them starts with ownership and acceptable delay. Replication, change tracking, and a scheduled merge job solve different versions of that problem.

Define Ownership Before Synchronizing Data

Name the source of truth for each table and decide whether updates flow one way or both. Two-way writes require conflict detection and a business rule for resolution. A transport mechanism cannot decide whether a newer timestamp or a local correction should win. Document deletion behavior and how keys map between databases.

I ask what happens when the link is down for a day. If the answer is unclear, the design for synchronizing data is not ready. A system that copies rows quickly can still copy the wrong winner with great efficiency.

Set Latency and Volume Targets

A dashboard copy that can be ten minutes behind differs from an operational replica that needs near-real-time updates. Count changed rows, peak bursts, row width, and expected growth. Include schema change frequency and the time available for reinitialization. These inputs determine whether a simple batch remains sensible.

I measure the change rate during a real business cycle. A nightly job can look fine on a test table and miss its window after month-end imports. Write the acceptable lag in plain terms that users can validate.

When Transactional Replication Fits

Transactional replication distributes changes from a publisher through a distributor to subscribers. It supports high-throughput server-to-server copy scenarios with relatively low latency. It also introduces agents, distribution storage, monitoring, and operational rules for schema changes. Subscriber write behavior needs explicit design.

Replication is a strong fit when supported topology and latency requirements align. I do not choose it merely because the name sounds like synchronization. A reporting subscriber with one-way updates is simpler than a multi-writer system that expects automatic conflict resolution.

When Change Tracking Fits

Change tracking records which rows changed without storing every intermediate value. A consumer asks for changes since a saved version, reads current rows, and applies them elsewhere. It can also report deleted keys. The consumer owns extraction, transport, apply logic, and its watermark. Retention must exceed the longest expected outage.

This query checks whether change tracking is enabled and its retention setting in the current database. It does not enable the feature. I verify the minimum valid version before each extract so cleanup cannot silently erase changes the consumer still needs.

SELECT DB_NAME(database_id) AS database_name,
       is_auto_cleanup_on,
       retention_period,
       retention_period_units_desc
FROM sys.change_tracking_databases
WHERE database_id = DB_ID();

When a Scheduled Job Fits

For small, low-frequency changes, a scheduled job can compare source and target by stable business key. Stage source rows, validate uniqueness, then update changed rows and insert missing rows. MERGE can express this compactly, but separate statements can be easier to diagnose under concurrency. Test both choices.

A full compare can become costly as tables grow. I use a modified-date or rowversion watermark only after checking what it misses. Deletes, backdated corrections, clock skew, and rows changed during extraction all need explicit handling. Simple does not mean casual.

Check Change Tracking Versions

CHANGE_TRACKING_CURRENT_VERSION returns the current database version. The minimum valid version for a tracked table tells a consumer whether its saved version is still usable. If the saved version is older, reinitialize rather than pretending an incremental run was complete. The table must have change tracking enabled for the second call to apply.

I store the last successfully applied version in the target, not the version seen before apply. Advance it only after the target transaction commits.

SELECT CHANGE_TRACKING_CURRENT_VERSION()
       AS current_change_version;

SELECT CHANGE_TRACKING_MIN_VALID_VERSION
       (OBJECT_ID(N'dbo.Customers'))
       AS minimum_valid_version;

Apply Changes Idempotently When Synchronizing Data

A sync batch can fail after writing some rows. A rerun should produce the same target state without duplicates. Unique keys, bounded transactions, and a batch identifier help. Keep the source change version and target commit together where possible. If the databases are on different servers, design around partial failure.

I test a batch that is interrupted halfway through. The restart path reveals more than a clean demonstration. A job that needs a DBA to guess where it stopped is not a synchronization system.

Monitor Lag and Data Quality

Track source version or commit time, last successful target apply, rows inserted, updated, and deleted, and error count. Add periodic reconciliation by key and important aggregates. A green job history only proves the process exited successfully; it does not prove the two databases agree.

I keep a small sample of mismatched rows for review, with sensitive data protected. If lag grows, separate extraction time from apply time. The fix differs when the source query is slow versus when target indexes make writes expensive.

Choose the Smallest Supported Design for Synchronizing Data

Use transactional replication for supported low-latency distribution, change tracking for an application-owned delta feed, and scheduled compare jobs for modest, tolerant workloads. Do not hide two-way conflict policy inside a SQL statement. Put it in a decision the business can explain.

Synchronizing data between databases is complete only when ownership, delay, deletion, retries, and reconciliation all have answers. The transport is one part. The boring part is proving both copies still mean the same thing on Tuesday morning.

Reconciliation is part of synchronization. Compare source and target keys and business totals at a fixed checkpoint. Lag metrics show whether changes are moving, but they do not prove that every row arrived correctly. A validation query should find missing, extra, and mismatched rows under the chosen ownership rule.

I document what happens during a source outage and a target outage separately. A queued change stream needs retention long enough for recovery. A scheduled extract needs a saved boundary and an idempotent replay. If retention expires, the process should stop and rebuild a baseline instead of continuing with a gap. Synchronization is successful only when the team can explain its current position and repair it.

If the target is behind, can you identify the last source change it applied and replay from that point?

Related reading on this blog: What is Transactional Replication Supported Version Matrix? Interview Question of the Week #274 and SQL Server: Error While Enabling CDC on a Table: The specified '@server' is invalid (valid values are returned by sp_helpserver).

Synchronization is not copying rows once, it is preserving an agreed state through change and failure.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.