Microsoft SQL Server to Snowflake: How to Move Your Data
Move Microsoft SQL Server into Snowflake with Airbyte. Why CDC depends on SQL Server Agent, the permission that prevents silent data loss, and retention settings.
Moving Microsoft SQL Server into Snowflake takes the database running your business and puts a copy somewhere analysts can work without touching production. SQL Server is usually the system of record for something that matters, and running reporting queries against it competes with the application your users depend on.
This guide covers the managed path with Airbyte. Change data capture is the reason to use this connector rather than a nightly export, and it rests on a piece of SQL Server infrastructure that can stop running without anybody noticing unless you grant one specific permission.
Microsoft SQL Server to Snowflake at a glance:
Why move data from Microsoft SQL Server to Snowflake?
Two situations account for most of these pipelines.
The first is protecting the application. Analytical queries against a transactional SQL Server instance compete with the requests your users are making, and the usual workaround of a read replica still ties reporting to a row-oriented engine that was never built for aggregation.
The second is joining. Your SQL Server database holds one part of the picture and marketing, support and finance data lives elsewhere, so a warehouse is where those meet. If you only need operational reporting on current data, SQL Server Reporting Services already does that.
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.
An extended CDC retention period. Airbyte recommends at least 10080 minutes, which is seven days, so the connector has room to resume after an outage. The default is considerably shorter and is the usual reason a working pipeline breaks after a quiet weekend.
On the Snowflake side you need a warehouse, database, schema and a role able to create tables. If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on both sides before you begin.
How do you build a Microsoft SQL Server to Snowflake 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 Snowflake destination
Click Destinations, then New Destination, and select Snowflake, following adding a destination. Supply the account identifier, warehouse, database, schema and role. Size the warehouse generously for the initial snapshot and scale it back afterwards, since the first sync is by far the heaviest.
Step 4: Choose sync modes and deduplicate
Click Connections, then New connection, select your tables and a sync mode each. Airbyte's CDC implementation uses at least once delivery, so a change record can arrive more than once and a deduplicating mode is what stops that showing up in your tables.
With CDC, deleted rows arrive carrying a deletion marker rather than vanishing, so append and deduped mirrors current SQL Server state while plain append preserves every version for anyone who wants history.
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 warehouse quietly stops 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, which turns silent data loss into an alert somebody acts on. It is described as optional in the setup and it is the difference between noticing in minutes and noticing in a month.
What will CDC not capture?
Several things, and they are worth auditing before you assume a table is covered. CDC cannot be enabled on a table with a clustered columnstore index, which rules out exactly the large analytical tables somebody may most want replicated.
Beyond that, the general CDC rules apply. Data must be in tables rather than views, most sources need a primary key for CDC incremental, and changes made with TRUNCATE or ALTER do not appear in the log and therefore never reach your destination. A nightly job that truncates and reloads a table is invisible to this pipeline.
New columns and schemas need a snapshot before CDC can track them, so adding a column is not automatically picked up. Treat a schema change on the source as something your pipeline needs to hear about, rather than assuming replication will work it out.
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 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.
Why can I not enable CDC on one of my tables?
Check for a clustered columnstore index, since CDC cannot be enabled on those tables. Views are also out, and most sources need a primary key for CDC incremental.
Can I do this without writing code?
The Airbyte setup is entirely UI-driven. The SQL Server side needs a DBA to run the CDC stored procedures and grant permissions, which is configuration rather than code.
Get your SQL Server data into Snowflake
Insist on VIEW SERVER STATE, because without it a stopped Agent is silent data loss rather than a failed sync. Raise CDC retention to seven days, audit your tables for columnstore indexes and missing primary keys, and use a deduplicating sync mode since change delivery is at least once.
Airbyte's connector catalog includes 700+ pre-built connectors, so operational data can sit beside the marketing and finance sources it needs to be judged against. For another relational source into the same warehouse, see MySQL to Snowflake, and for a warehouse to warehouse move, Amazon Redshift to Snowflake.
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.