SYNCHRONISING SQL database

There is no single SQL statement that safely “synchronizes” two arbitrary SQL Server databases. The right approach depends on direction (one-way vs bidirectional), latency (real‑time vs scheduled), whether schemas must stay identical, conflict resolution, the SQL Server version/edition, and whether the goal is high availability or data distribution. @RahulV asked about query/code — most solutions are configuration or small programs (or ETL packages), not one-shot SQL.

Common approaches (short summary and typical use cases):

• Replication (snapshot, transactional, merge) — good for distributing data or supporting disconnected/offline scenarios; merge supports bi-directional conflict resolution. See Microsoft’s replication overview: Replication overview.

• Availability Groups / Always On — for high-availability and readable secondaries (near real-time copies of whole databases), better for failover/read-scaling than row-level sync: Availability groups.

• Log shipping and database backups/restores — simple, reliable one-way copies for DR or periodic sync; not suitable for low-latency two-way sync: Log shipping.

• SSIS / custom ETL or T-SQL (MERGE, scheduled jobs) — flexible for scheduled transforms or selective table syncs; needs conflict/PK handling.

• Change Tracking / Change Data Capture — use to capture what changed and apply deltas, useful for custom sync processes: Change Data Capture.

Practical notes and gotchas: plan for identity/PK conflicts, unique constraints, collation differences, and triggers that might loop. Test on copies, measure latency and throughput, and log conflict resolution. Check SQL Server edition/feature availability before picking a feature (some features changed across versions). @kvprajapati’s MSDN pointer is a good start; the links above point to current Microsoft docs for each major option and help decide which tool fits the scenario.