Microsoft SQL Server to ClickHouse: How to Move Your Data
Move Microsoft SQL Server into ClickHouse with Airbyte. Why CDC depends on SQL Server Agent, and why stale figures and duplicate rows have different causes.
Moving Microsoft SQL Server into ClickHouse puts transactional data behind an engine fast enough to sit under a live dashboard, at a fraction of the operational overhead of a warehouse. SQL Server is excellent at serving an application and poor at aggregating millions of rows while doing so.
Two behaviours shape this build, and both can make a count look wrong. Change data capture rests on a SQL Server service that can stop silently, and ClickHouse collapses duplicate rows on its own schedule rather than yours.
Microsoft SQL Server to ClickHouse at a glance:
Why move data from Microsoft SQL Server to ClickHouse?
Two situations account for most of these pipelines.
The first is serving analytics fast without a warehouse. If ClickHouse already sits behind an internal tool or a customer-facing dashboard, putting operational data next to the event data already there means one engine answering both in milliseconds.
The second is protecting the application. Aggregations against a transactional SQL Server instance compete with the requests your users are making, and a columnar copy answers those queries without anybody touching production.
What do you need before you start?
Most of this is work a database administrator does, so book their time before anything else:
An edition that supports CDC, with the Agent running. Change data capture depends on SQL Server Agent jobs, and Express edition does not include the Agent at all. Check it is running before planning around CDC. The MSSQL source documentation covers the setup.
CDC enabled at database level, then per table. These are separate steps using SQL Server's built-in stored procedures, and an administrator must enable it on each table you want to capture. A table nobody enabled is simply not tracked.
A read-only user with SELECT on the cdc schema, plus VIEW SERVER STATE. The second is technically optional and worth insisting on, because it is what lets Airbyte check whether the Agent is running. The reason that matters is covered below.
A ClickHouse instance and credentials. With permission to create tables in the target database. The destination writes through the native protocol into typed columns, so SQL Server's declared types carry across cleanly.
You also need an extended CDC retention period, since Airbyte recommends at least 10080 minutes, which is seven days. The default is considerably shorter and is the usual reason a working pipeline breaks after a quiet weekend.
How do you build a Microsoft SQL Server to ClickHouse pipeline in Airbyte?
Step 1: Prepare SQL Server with your DBA
Enable CDC on the database, then on each table you want, raise the retention period to seven days, and create the read-only user with SELECT on the cdc schema and VIEW SERVER STATE. Doing all of it in one session is far easier than discovering each requirement separately over a fortnight.
Step 2: Configure the MSSQL source
Click Sources in the left navigation, then New Source, and select Microsoft SQL Server, following adding a source. Supply the host, port, database, schema and credentials, then choose change data capture as the replication method rather than leaving it on the cursor-based default.
Step 3: Configure the ClickHouse destination
Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply host, port, database and credentials. Deduplication uses the ReplacingMergeTree table engine, and your sort order is worth choosing deliberately since it affects query performance more than anything in the pipeline.
Step 4: Use a deduplicating sync mode
Click Connections, then New connection, select your tables and choose incremental append and deduped as the sync mode. CDC uses at least once delivery, so a change record can arrive more than once, and deduplication is what stops that reaching your tables.
Then put any query that needs an exact count behind a view using FINAL, for the reason set out below.
Why does CDC depend on the SQL Server Agent?
Because the Agent runs the jobs that do the work. SQL Server's CDC reads the transaction log and writes each change, with its before and after values, into change tables, and those jobs are Agent jobs. The connector reads the change tables rather than the log itself.
So if the Agent stops, the change tables stop being updated while everything else carries on working. Your sync runs, finds no new rows, and reports success. Nothing is broken in any way a monitoring dashboard would show, and your tables quietly stop reflecting reality.
That is exactly what the VIEW SERVER STATE permission prevents. With it, Airbyte can check whether the Agent is running and fail the sync when it is not. On a pipeline feeding a live dashboard, that is the difference between somebody noticing in minutes and a product showing stale figures for a month.
Why can a count be wrong for two different reasons?
Because this pairing stacks two independent behaviours, and keeping them apart saves a great deal of diagnostic time. The first is upstream: a stopped Agent means no new change records, so your figures are stale rather than duplicated, and the fix is the permission above.
The second is in ClickHouse. ReplacingMergeTree collapses duplicate rows during background merges rather than on write, so a query run shortly after a sync can legitimately return rows that the same query returns cleanly an hour later. That is expected behaviour rather than a fault.
The fixes differ accordingly. Staleness is a source problem you alert on. Merge timing is an artefact you hide, by adding FINAL where exact figures matter and telling your analysts which tables are safe to query directly. Knowing which you are looking at is the whole point of separating them.
Frequently asked questions
Does SQL Server Express support CDC?
No. CDC depends on SQL Server Agent jobs and Express does not include the Agent, so you are limited to cursor-based incremental replication without delete capture.
My sync succeeds but no new rows arrive. What should I check?
Whether SQL Server Agent is running. If it stops, the change tables stop updating and syncs report success with nothing new. Granting VIEW SERVER STATE makes this fail loudly instead.
Why does my table contain duplicate rows?
ReplacingMergeTree removes duplicates during background merges rather than immediately. Query with FINAL if you need guaranteed deduplication before those merges run.
Why did CDC break after a quiet period?
The change data was probably cleaned up before the connector could resume. Airbyte recommends extending CDC retention to at least 10080 minutes, which is seven days.
Can I do this without writing code?
The Airbyte setup is UI-driven and the SQL Server side is DBA configuration. You will want ClickHouse views using FINAL where exact counts matter, which is short SQL.
Get your SQL Server data into ClickHouse
Insist on VIEW SERVER STATE, raise CDC retention to seven days, use a deduplicating sync mode, and put exact counts behind a view using FINAL. Then make sure whoever queries these tables knows that stale figures and duplicate rows have different causes and different fixes.
Airbyte's connector catalog includes 700+ pre-built connectors, so operational data can sit beside the events that explain it. For the same source in a warehouse, see Microsoft SQL Server to Snowflake, and for another relational source in the same engine, CockroachDB to ClickHouse.
Integrate with 700+ apps using Airbyte
Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.