Microsoft SQL Server to BigQuery: How to Move Your Data

Moving Microsoft SQL Server into BigQuery 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 BigQuery at a glance:

Why move data from Microsoft SQL Server to BigQuery?

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 operational data to everything already in BigQuery. Advertising, web analytics and product data frequently live there, and putting your transactional records beside them turns questions that spanned two systems into a single query.

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 BigQuery service account and a decided dataset location. With the BigQuery User and BigQuery Data Editor roles. Queries can only reference datasets in the same physical location and that cannot be changed after creation, so match it to whatever you intend to join against.

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 BigQuery 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 BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project, dataset and service account key, then pick a loading method. Cloud Storage staging is worth considering for the initial snapshot of a production database, which is the heaviest read this pipeline will do.

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.

How do you keep query costs sensible?

By filtering on the partitioning column, because BigQuery bills for the bytes a query scans and a replicated production database accumulates indefinitely. Output tables are partitioned daily on the extraction timestamp and clustered on that column plus the primary keys.

That convention needs stating explicitly here, because the people querying this data are used to SQL Server, where scanning a large table cost time rather than money. The habit of narrowing a query for cost reasons does not transfer, and nobody acquires it by accident.

If you chose plain append to keep every version of every row, this matters more again, since the table holds the full history of changes rather than current state. Establish the convention while there is one query to change rather than a migrated set of reports.

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.

How do I keep BigQuery costs down?

Filter on the extraction timestamp, which is the partitioning column. Tell analysts explicitly, since SQL Server's cost model does not train that habit.

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 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 BigQuery

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, create the dataset in the right location since that is permanent, use a deduplicating sync mode, and teach analysts to filter on the partitioning column.

Airbyte's connector catalog includes 700+ pre-built connectors, so operational data can sit beside the commercial sources that explain it. For the same source in another warehouse, see Microsoft SQL Server to Snowflake, and for another relational source in BigQuery, MySQL to BigQuery.

Start syncing now →