SQL Server 2016 reached end of support on July 14, 2026, and many of those instances still feed the reports a business runs on. Many of those teams are moving the data to Snowflake, BigQuery, Redshift, or Databricks. Getting it there is a project with an end date, while keeping it fresh is a job that runs every day.
Nightly batch loads leave dashboards hours behind the source, and full-table extracts add read load to the production server. Real-time replication closes that gap when the pipeline holds up through schema changes, traffic spikes, and cutover day.
This guide’s checklist covers the 10 items engineering and data teams should sign off on before go-live, with a pass or fail check under each one.
1. Log-based change data capture method
Quick definition: change data capture (CDC)
Change data capture (CDC) captures and moves only changed records by reading the database transaction log, which supports near-real-time and efficient data synchronization.
SQL Server’s four native replication types (snapshot, transactional, merge, and peer-to-peer) keep SQL Server copies in sync. CDC produces a change feed any target can consume, which makes it the standard for cloud warehouse pipelines.
Because CDC reads committed changes from the transaction log, it adds far less load than polling tables with queries. Before you rely on it, confirm three prerequisites.
CDC requires SQL Server Standard, Enterprise, or Azure SQL Managed Instance.
The capture and cleanup jobs run on SQL Server Agent, so a stopped Agent means no new changes.
Change rows are kept for 4,320 minutes (72 hours) by default. A pipeline that stays down longer misses purged changes and needs a full re-snapshot of the affected tables.
SQL Server 2025 also previews Change Event Streaming to Azure Event Hubs and Fabric Eventstream.
CData Sync builds on SQL Server’s native CDC or change tracking, with no custom triggers to write. When both are enabled on a table, Sync uses CDC. It then replicates only changed rows to Snowflake, BigQuery, Redshift, Databricks, and dozens of other destinations.
Sign-off check. CDC is enabled at the database and table level, SQL Server Agent is monitored, and the retention window is longer than your worst-case outage.
2. Define latency targets and service level agreements
Replication latency is the time from a committed transaction in SQL Server to a queryable row in the warehouse. Sub-second targets suit fraud alerts and operational systems that act on events. Sub-minute targets cover most BI dashboards, operational analytics, and ML feature feeds.
Every stage adds time. The capture job has built-in latency between commit and change table, the pipeline runs on an interval, and the warehouse needs time to load and merge.
Set targets per table tier. Order tables may need sub-minute freshness while reference data can refresh hourly. Verify every vendor claim with a production-like test on your own volume.
Sign-off check. Each table tier has a written latency target that data owners approved and that a production-volume test confirmed.
3. Confirm automatic schema evolution handling
Quick definition: schema evolution
Schema evolution is the automatic detection and propagation of schema changes, such as new columns or type modifications, without manual intervention or breaking data pipelines.
Native SQL Server CDC doesn’t follow DDL on its own. Per Microsoft’s CDC documentation, the capture process ignores columns added after a capture instance was created, so new columns never reach the change table.
DBAs handle this with a second capture instance, created before the first is dropped. CData Sync 26.3 detects that second instance, drains the old one, and transitions automatically with no re-sync of the job. With change tracking, Sync updates the destination table automatically when a column is added or a data type changes.
Run each of these DDL events in staging before launch.
Add a nullable column and confirm it populates at the destination with real values instead of NULLs.
Drop a column and confirm downstream reports still run.
Widen a data type, such as varchar(50) to varchar(200).
Add a new table to the capture set mid-replication.
Sign-off check. Every DDL event in the test plan completed in staging with no data loss and no full refresh.
4. Validate throughput and backpressure capacity
Throughput testing measures the maximum ingestion rate for both typical transactions and bursty loads. Include large deletes, bulk inserts from nightly jobs, long-running transactions, and log-heavy maintenance such as index rebuilds.
Quick definition: backpressure
Backpressure is the system’s ability to throttle or queue incoming changes when downstream components are temporarily overwhelmed.
By default, the SQL Server capture job processes up to 500 transactions per scan cycle with a 5-second wait between cycles, so a burst builds capture lag. The transaction log also can’t truncate until the capture job reads it, so sustained lag grows the log.
Watch sys.dm_cdc_log_scan_sessions during tests, and time how long the pipeline takes to return to normal lag.
Sign-off check. A peak-hour replay finished with lag back under SLA within the agreed recovery time and no log growth alerts.
5. Verify target cloud data warehouse compatibility
The target must support the patterns your workloads already use, including joins, subqueries, materialized views, ANSI SQL, and your BI tool connectors. Teams moving to Snowflake can follow these SQL Server to Snowflake pipeline practices.
Warehouse | Scaling model | Compute billing | Best fit |
Snowflake | Separate compute and storage, multi-cloud | Per second, 60-second minimum per warehouse start | Mixed BI and bursty workloads |
Google BigQuery | Serverless | $6.25 per TiB scanned after 1 TiB free, or slot-hours | Ad hoc analytics and Google Cloud ML |
Amazon Redshift | Provisioned or Serverless | RPU-hours per second, 60-second minimum | AWS-native estates |
Databricks SQL | Lakehouse with Classic, Pro, and Serverless SQL warehouses | DBUs per hour, metered per second | Shared BI and ML on one copy of data |
ClickHouse Cloud | Columnar OLAP, compute scales to zero when idle | Per compute-unit-hour, metered per minute | High-concurrency, real-time analytics |
Billing minimums affect CDC costs. A short sync interval wakes the warehouse more often, and each resume carries a minimum charge on Snowflake and Redshift Serverless. Set the sync interval from the latency target in item 2, even when a faster setting is available.
Sign-off check. Your top 20 production reports return correct results on the target within acceptable query times.
6. Evaluate cost models and observability features
BigQuery charges per TiB scanned, Snowflake per credit, Redshift by node or RPU, and some platforms by subscription. Per-scan models reward partitioned tables, while credit models reward right-sized warehouses that suspend quickly.
Model monthly cost under three workloads, which are continuous sync, batch ETL, and bursts, and include replication costs in the same model. Sync licenses by connection, and its Professional and Enterprise tiers carry no row cap, so CDC volume spikes don’t raise the replication bill. Sync 26.4 also lets one job carry multiple schedule rules, such as more frequent CDC on weekdays and less on weekends.
Quick definition: observability tools
Observability tools provide monitoring, alerting, and usage visualization to optimize spend and diagnose pipeline failures quickly.
On the source, sys.dm_cdc_log_scan_sessions and sys.dm_cdc_errors cover capture. In the pipeline, Sync’s PARTIAL_SUCCESS status shows which tables landed, and when retry is configured, Sync retries only the failed portion.
Sign-off check. Monthly cost is modeled for all three workloads, and alerts are set on capture lag, job errors, and warehouse spend.
7. Ensure security and compliance requirements
Confirm these controls at every hop from SQL Server to the warehouse.
Encryption in transit and at rest on the source, pipeline, and target
VPC or private-link connectivity, with granular network ACLs
Strict role-based access on the source, pipeline, and warehouse
A CDC gating role that limits who can read change data
Map certifications such as SOC 2, GDPR, HIPAA, and FedRAMP to each component and region, including data residency. Then map audit, retention, and masking policies to platform capabilities.
Sync 26.4 adds vaults, so jobs reference secrets by name instead of storing them in connection settings. Sync also supports Always Encrypted columns through its JDBC driver.
Sign-off check. Security has reviewed the network path, credential storage, and access roles, and masking rules apply to every regulated column.
8. Choose deployment models and plan operations
Quick definition: serverless vs. self-hosted
Serverless managed services handle infrastructure and scaling, while self-hosted deployments offer higher control over security, geography, and maintenance.
Choose based on operational burden, regulatory requirements, and team skills. Sync deploys in your cloud, inside your VPC, in your on-premises network, or as a managed service, with the same CDC pipelines in each. On-premises instances deliver to cloud targets over outbound-only connections.
Write runbooks for the failures CDC pipelines run into most often.
SQL Server Agent stops and capture halts
Downtime approaches the retention window
An availability group failover leaves the new primary without capture jobs
A restore to another server or under a new name drops CDC unless it uses the KEEP_CDC option
A warehouse load fails mid-batch
Sign-off check. Monitoring, alerting, and rollback runbooks exist for each failure above, and the on-call team has rehearsed them.
9. Conduct performance testing under production load
Test with production volume and query patterns. The essential cases are query latency for BI dashboards and complex joins, CDC throughput under heavy load, behavior during schema changes, and error rates across a full business day.
Run staged load tests, model concurrency at peak user count, and simulate failover. CDC jobs live in msdb, which an availability group doesn’t replicate, so Microsoft’s Always On guidance recommends creating them on every failover target in advance and keeping them disabled until that replica becomes primary.
Report results in a single table stakeholders can sign. The measured values and go or no-go calls below are illustrative, so replace them with your own results.
Metric | Example target | Measured (examples) | Go or no-go |
Latency, tier 1 tables | Under 60 seconds | 38 seconds | Go |
Peak-hour recovery time | Under 15 minutes | 22 minutes | No-go |
Dashboard query time | Under 5 seconds | 3.1 seconds | Go |
Job error rate | Zero unrecovered | Zero | Go |
Sign-off check. Stakeholders signed the go or no-go table with measured results against every SLA.
10. Plan migration and cutover with zero downtime
A phased cutover protects business continuity and proves data parity before anyone switches over.
Run the initial load, then start CDC so changes stream continuously.
Run source and target in parallel and validate daily.
Restrict writes on the source during a short maintenance window.
Let CDC catch up until lag reaches zero.
Switch BI tools and application endpoints to the warehouse.
Monitor closely for 48 to 72 hours after cutover.
Validate with table-level row counts, aggregated totals such as daily order amounts, and critical report parity. Set rollback criteria in advance, such as a row-count mismatch above a threshold, so the decision isn’t made under pressure.
Replicate SQL Server in real time with CData Sync
CData Sync covers the pipeline side of this checklist from one platform. It reads SQL Server’s native CDC, handles capture instance changes without a full refresh, and deploys wherever your data lives.
Your team spends cutover week validating data instead of fixing pipelines. Start a free 30-day trial and run your first SQL Server CDC job today.
Frequently asked questions
What is the safest strategy for real-time SQL Server to cloud replication?
The safest approach is a phased migration with parallel validation, rehearsed rollback, and a scheduled cutover. Running source and target side by side until CDC lag reaches zero confirms consistency before the switch.
How do I enable and configure CDC on SQL Server for continuous replication?
Enable CDC on the database, which requires sysadmin, then on each table, which requires db_owner. SQL Server Agent must be running. Set a gating role for readers and a retention period that covers your longest expected outage.
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Orders',
@role_name = N'cdc_reader';
What network and security measures are critical for reliable replication?
Use a VPN or private links, encrypt data in transit and at rest, enforce network access controls, store credentials in a vault, and keep audit logging on.
How do I monitor and maintain data freshness and replication latency?
Track capture lag with sys.dm_cdc_log_scan_sessions and pipeline lag with job metrics. Alert when lag nears your SLA, and check freshness on critical tables regularly.
What validation and rollback steps ensure a smooth migration cutover?
Compare row counts, aggregated totals, and critical reports between source and target. Schedule a maintenance window for the final catch-up, and keep a documented rollback plan with clear triggers.