Microsoft SQL Server to Amazon Redshift: How to Move Your Data
Move Microsoft SQL Server into Amazon Redshift with Airbyte. Why CDC depends on SQL Server Agent, the permission preventing silent data loss, and S3 staging.
Moving Microsoft SQL Server into Amazon Redshift 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.
Two things shape this build. Change data capture rests on a piece of SQL Server infrastructure that can stop running without anybody noticing, and Redshift cannot be loaded without a staging bucket, so a Microsoft source acquires an AWS prerequisite.
Microsoft SQL Server to Amazon Redshift at a glance:
Why move data from Microsoft SQL Server to Amazon Redshift?
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 a cross-vendor reality. Plenty of organisations run Microsoft databases and an AWS analytical estate, so operational data lives in one place and reporting happens in another. The pipeline is the bridge between two decisions nobody made together.
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.
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 S3 staging bucket and IAM permissions. The Redshift destination writes data to S3 as files with a manifest, then issues a COPY command, which is Redshift's own recommended approach. You need put, get, delete and list on the bucket, in the same region as the cluster.
An extended CDC retention period and Redshift entities. Airbyte recommends at least 10080 minutes, which is seven days. On the warehouse side you need a database, a schema and a user able to write data and manage staging operations.
Note also that an SSH tunnel covers the SQL connection only. Staging traffic reaches S3 over HTTPS regardless, so a network design assuming everything passes through a bastion is wrong.
How do you build a Microsoft SQL Server to Amazon Redshift 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 Redshift destination
Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the cluster host, database, schema and credentials, along with your S3 bucket and IAM key. Staging files are removed after a successful COPY by default, and can be retained if you need them for debugging.
Step 4: Choose sync modes and set your keys
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.
Set sort and distribution keys on the resulting tables too, since those choices affect query performance considerably more than anything in the pipeline does.
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.
Why does a Microsoft source need an AWS bucket?
Because the requirement comes from the destination rather than the source. Redshift is designed to be loaded in bulk from S3, so the connector writes files with a manifest and then issues a COPY command pointing at them. That is Redshift's own recommended path and there is no direct insert alternative.
On a database replication pipeline this matters more than on a small API source, because the initial snapshot of a production database can be substantial and all of it passes through the bucket. Put it in the same region as your cluster, and budget for the transfer rather than discovering it on a bill.
It also affects your network design, since an SSH tunnel covers the SQL connection to the cluster and not the staging traffic. On a pipeline carrying production data from a Microsoft estate, that is worth raising before somebody discovers it in a security review.
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.
Do I need an S3 bucket to load into Redshift?
Yes. The destination stages data in S3 and loads it with COPY, which is Redshift's recommended approach. Put the bucket in the same region as your cluster.
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 Amazon Redshift
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, provision the staging bucket in your cluster's region, use a deduplicating sync mode, and set sort and distribution keys deliberately.
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 landing in Redshift, MySQL to Amazon Redshift.
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.