SQL Server replication: copy every change to new systems without straining production

When an organisation modernises, the new pieces need SQL Server's data, and they need it current. A nightly export answers yesterday's question; SQL Server change data capture delivers each committed change, in commit order, while the source keeps working. It is used in four situations:

• A phased migration. A new database is loaded once, then kept in step through a parallel run. Our SQL Server to PostgreSQL migration page covers that route in full.
• New services. New applications read changes from a stream, not from the production database.
• Reporting. A separate copy is fed continuously, so heavy queries leave production.
• Two systems in parallel. An old and a new application both take writes for a period.

The general mechanics are in our change data capture guide. This page covers the SQL Server side; the same questions for other engines are on our pages on PostgreSQL replication and MySQL replication.

RelatedSQL Server to PostgreSQL migration pagechange data capture guide

A DBA's first question is fair: will capture slow down production? Log-based capture adds little load compared with polling or triggers, but it still asks things of the SQL Server team. Agree them first:

• The capture account and exactly what it may read, on which databases and tables.
• Log space. Changes must stay readable until captured, so a long pause can make the transaction log grow. Size it and set an alert.
• The initial load. When it runs, from which recorded point, and how much load it may place on production.
• Maintenance. How capture fits with log backups, index work and patching.
• High availability. What the capture does when the server fails over to another node, and where it resumes.
• Schema changes. Who tells the replication team when a table or column changes.

iblync throttles the initial load and any catch-up, inside a limit your DBAs set.

Streaming SQL Server to Kafka lets new services react to an order or a status change without touching the production database. Apache Kafka is among the systems iblync's website lists; the pairing is confirmed in scoping. What decides whether the stream can be trusted:

• Topics and keys. Usually one topic per table, keyed by the primary key, so changes to one record stay in order.
• Duplicates. After a restart a change can arrive twice, so consumers must handle repeats safely.
• Schema changes. A new or renamed column changes the shape of the event; agree how consumers learn about it.
• Sensitive columns. Decide which columns may leave SQL Server, and mask or drop the rest.

A permanent stream is an integration: give it an owner, monitoring and change management.

iblync is Interkey's own data migration and replication platform, built in Saudi Arabia. SQL Server and PostgreSQL are on its published connector list. It brings the parts that protect production and the target:

• Continuous replication after one initial load, and two-way synchronisation where both sides take writes.
• Throttling, so replication stays inside the agreed load.
• A queue that holds changes while the target is unavailable and delivers them when it returns.
• Resume and validation. iblync's site states that interrupted work resumes where it stopped and that validation uses checksums and row counts.

Relatediblyncreal-time replication page

A replication stream is production data in motion, so residency applies to it too. Many Saudi organisations must keep certain data inside the Kingdom under the National Cybersecurity Authority's controls (nca.gov.sa) and the Personal Data Protection Law overseen by SDAIA (sdaia.gov.sa). Where the capture process, the queue and the target run is confirmed per engagement. Which data must stay, and why, is settled before any data moves.

Support comes from Riyadh, in Arabic and English, working beside your DBAs from scoping through operation.

Relatednca.gov.sasdaia.gov.sa

If a target only needs yesterday's figures, a scheduled batch load is simpler to run. If you are copying one SQL Server database to another SQL Server and its built-in features already do the job, keep them, and iblync may not be needed. And a replicated copy is not a backup: a table deleted by mistake is deleted on the target too.

We publish no price or duration, because the estate decides both. The drivers are:

• databases and tables in scope, and daily change volume;
• the number of targets and how far their data models are from SQL Server's;
• how closely the target must follow the source;
• approvals from the DBA and security teams;
• where the bridge must run, and how the source fails over;
• whether the stream is temporary, for a migration, or permanent.

Scoping turns these into a schedule and a quotation.