How to Move Data from MS SQL Server to Redshift: Step-by-Step Guide

As data volumes increase, Microsoft SQL Server can fall short for large analytical queries. Many teams move analytics workloads to Amazon Redshift to improve query performance, scalability, and cost efficiency.

This guide explains how to move data from MS SQL Server to Redshift using methods suited to different requirements and use cases, including manual data loading, managed migration services, and no-code ETL options.

It also outlines when each approach is most suitable, along with key limitations and considerations for long-term reliability.

Note: For SQL Server to Redshift migrations, schema compatibility and performance optimization must be carefully validated to ensure reliable analytical workloads.

Want to build an MS SQL Server to Redshift pipeline now? Hevo gets you started in minutes!

Comparison Table

Criteria	Hevo Data	Manual export	AWS DMS
Best for	No-code, reliable pipelines with transparent pricing	One-time data loads of small controlled batch migrations	Enterprises requiring managed migration with optional continuous replication
Ease of use	Easy	Difficult	Moderate
Setup time	Within minutes	High	Moderate
Real-time sync	✅	❌	✅
Automation	Fully automated	Manual	Managed replication
Maintenance needs	Minimal	High	Moderate
Scalability	Auto-scaling capabilities	Limited scalability without significant scripting	Scales based on replication instance size and AWS infrastructure
Handle schema changes	Auto-detects and adapts	Manual scripting required	Limited support

What is MS SQL Server?

Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is designed to store and retrieve data as requested by other software applications, which can run on the same computer or connect to the database server over a network.

Key features

• Mission-critical OLTP: Optimized for high-frequency transactional workloads to ensure ACID compliance and low-latency processing.
• Multi-language support: Enables application development using T-SQL (Transact-SQL), .NET languages, Python, R, and other supported interfaces.
• Integrated BI stack: Powers data warehousing, analytics, and reporting through tools like SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS).
• High availability and recovery: Supports failover clustering and log shipping to minimize downtime and data loss.
• In-memory performance: Features In-Memory OLTP and Columnstore indexes to accelerate query performance by up to 100x without changing application code.

What is Amazon Redshift?

Amazon Redshift is a cloud-based data warehouse service offered by Amazon Web Services (AWS). It’s designed to handle massive amounts of data and analyze it efficiently to gain insights. Its pay-as-you-go pricing model makes it an affordable choice for many users.

Key features

• Massively Parallel Processing (MPP): Distributes queries across multiple compute nodes to deliver high-speed performance even on petabyte-scale workloads.
• Columnar storage engine: Organizes data by column rather than row, which significantly reduces disk I/O and accelerates queries that aggregate large datasets.
• Redshift Serverless: Automatically provisions and scales compute capacity based on workload demands. This eliminates the need to manage clusters or manually resize nodes.
• SQL-based interoperability: Supports standard SQL and integrates natively with BI tools like Amazon QuickSight and Tableau.
• AWS ecosystem integration: Works natively with services like Amazon S3, AWS Glue, and IAM to simplify data ingestion, security, and governance.

Understanding the Methods to Connect SQL Server to Redshift

A solid understanding of the different methods for migrating SQL Server to Redshift can help you make an informed decision on the right choice.Method 1: Using Hevo Data to connect SQL Server to Redshift

Specialized ETL tools for SQL Server and Redshift can simplify the migration process with smooth data extraction, transformation, and loading.

Here’s how you can use Hevo to transfer your MS SQL Server data to Redshift.

Prerequisites:

• Running SQL Server version 12 or higher.
• Database user with SELECT privileges for both SQL Server and Redshift. 
• VIEW CHANGE TRACKING and ALTER DATABASE are also required for Change Tracking or Table mode in SQL Server.
• A running Amazon Redshift instance with a database hostname and port number.
• Hevo’s IP addresses whitelisted.
• Hevo user with Team Collaborator or admin role, excluding Billing Administrator, for creating the Destination.

Step 1: Configure MS SQL Server as your source

1. Click PIPELINES in the Navigation Bar.
2. Click + CREATE in the Pipelines List View.
3. In the Select Source Type page, select the SQL Server variant.
4. In the Configure your SQL Server Source page, specify the following:
   • Pipeline name.
   • Database Host, Port, User, Password, and Name.
   • Schema Name.

5. Connecting through SSH is optional, but it is recommended for added security.
6. Click Test Connection.
7. Select Test & Continue to save.

You have successfully created SQL Server as a source. Now, let’s set up your Redshift destination.

Step 2: Integrate data into Redshift

1. Click DESTINATIONS in the Navigation Bar.
2. Click + CREATE in the Destinations List View.
3. In the Add Destination page, select Amazon Redshift.
4. In the Configure your Amazon Redshift Destination page, specify the following:
   • Destination name
   • Paste the Redshift connection string (same as the hostname URL from the AWS Console). It auto-populates host, port, and database name.
   • Database user, password
   • Schema Name (Optional)

5. Enable SSH for an additional level of security by not exposing your Redshift setup to the public.

Note: If this option is disabled, you need to whitelist Hevo’s IP addresses.

6. Click Test Connection.
7. Click Save & Continue.

And that’s it! All you had to do was enter the corresponding credentials to implement this fully automated data pipeline without using any code.

Method 2: Using custom ETL scripts to connect SQL Server to Redshift

This approach uses the Bulk Copy Program (BCP) command-line utility, an Amazon S3 bucket, and the Redshift COPY command to transfer data from SQL Server to Redshift.

Here’s how you can implement this approach step-by-step.

Prerequisites:

1. BCP command-line utility installed and accessible via the system PATH.
2. Network access and credentials to read data from the source SQL Server database.
3. An Amazon S3 bucket with write permissions to stage exported data files.
4. An Amazon Redshift cluster with an IAM role that grants read access to the S3 bucket.
5. AWS CLI installed and configured with sufficient permissions to upload files to S3.

Step 1: Generate a csv file using the BCP command

Open your command prompt and navigate to the modern BCP tools directory or ensure bcp is available in your system PATH. For example:

<drive>:\Program Files\Microsoft SQL Server\Client SDK\ODBC\180\Tools\Binn

Run the BCP command to generate the output file of the SQL server table Sales

bcp "sales.orders" out "C:\export\orders.csv" -c -t, -S "ServerName" -d "DatabaseName" -T

For SQL authentication use: 

bcp "sales.orders" out "C:\export\orders.csv" -c -t, -S "ServerName" -d "DatabaseName" -U "Username" -P "Password"

Step 2: Upload the generated file to the S3 bucket

Redshift requires files to be staged in cloud storage. Using the AWS CLI is the fastest way to handle large production files.

For this, you need to create an S3 bucket.

1. Go to the AWS S3 Console.
2. Click Create Bucket, enter a name, and select your region.
3. Open the bucket once it’s created.
4. Click Actions → Create Folder and give it a name (e.g., sql-exports/).

Now, you can upload the exported file. Run the following command to upload the file:

aws s3 cp "C:\export\orders.csv" "s3://s3bucket011/sql-exports/orders.csv"

Once the file is uploaded, you can also create a table schema.

CREATE TABLE sales.orders (  order_id INT NOT NULL SORTKEY,  customer_id INT,  order_status INT,  order_date DATE,  required_date DATE,  shipped_date DATE,  store_id INT DISTKEY,  staff_id INT) DISTSTYLE AUTO;

After running the above query, a table structure will be created within Redshift with no records.  To check this, run the following query:

Select * from sales. orders

Step 3: Load data from S3 to Redshift using the COPY command

Use the following COPY command to transfer the data from the selected S3 bucket into Redshift.

COPY sales.orders FROM 's3://s3bucket011/sql-exports/orders.csv' IAM_ROLE 'arn:aws:iam::0123456789012:role/MyRedshiftRole'FORMAT AS CSVEMPTYASNULLBLANKSASNULLDATEFORMAT 'auto';

You will need to confirm if the data has loaded successfully. You can do that by running the query.

Select count(*) from sales. orders;

This should return the total number of records inserted.

This completes the manual migration of data from MS SQL Server to Redshift.

Limitations of using custom ETL scripts:

• In cases where you need to move data once or in batches, the custom ETL script method works well. But this approach becomes extremely tedious for copying data from MS SQL to Redshift in real-time.
• If you are dealing with huge amounts of data, you will need to perform an incremental load (change data capture), which requires additional steps to an already complex process.
• Transforming data before loading it into Redshift is difficult.
• When you write code to extract a subset of data, the scripts often break as the source schema changes. This can result in data loss.

The process mentioned above is often harder to implement and maintain. This impacts the consistency and availability of your data in Amazon Redshift.

This is where Hevo makes it easier for you by offering a clear and fully managed solution. Build your first pipeline now!

Method 3: Using AWS Database Migration Service (DMS)

AWS Database Migration Service provides a fully managed migration solution that minimizes downtime and handles both one-time migrations and continuous data replication. This method is ideal for enterprise teams migrating multiple schemas through table mapping rules or requiring minimal disruption during the transition.

Here’s how you can implement it:

Prerequisites:

• AWS account with permissions to create DMS resources.
• SQL Server accessible from AWS.
• A running, accessible Amazon Redshift cluster.
• VPC connectivity configured between DMS, SQL Server, and Redshift.
• IAM role with permissions for DMS to access S3 (DMS stages data in S3 during migration).

Step 1: Create a DMS replication instance

• Open the AWS DMS Console.
• Click Replication instances → Create.
• Choose the instance class and storage.
• Place it in the same VPC as Redshift (or with proper connectivity).
• Create and wait until the status shows Available.

Step 2: Create the source endpoint (SQL Server)

1. Go to Endpoints → Create endpoint.
2. Select Source.
3. Engine type: Microsoft SQL Server.
4. Provide:
   • Server name
   • Port (default 1433)
   • Username & password
   • Database name
5. Test connection → Save.

Step 3: Create the target endpoint (Amazon Redshift) 

1. Create a new endpoint.
2. Select Target.
3. Engine type: Amazon Redshift.
4. Provide:
   • Redshift cluster endpoint
   • Port (default 5439)
   • Database name
   • Username & password.
5. Specify IAM role for S3 access (DMS stages data in S3).
6. Test connection → Save.

Step 4: Create a migration task 

1. Go to Database migration tasks → Create task.
2. Choose:
   • Replication instance
   • Source endpoint
   • Target endpoint
3. Migration type:
   • Full load (one-time migration), or
   • Full load + CDC (ongoing replication)

Note: For ongoing replication, ensure SQL Server CDC is enabled, and permissions are in place.

4. Select tables (schema/table selection rules).
5. Leave the advanced settings default unless required.
6. Create task.
7. Start task

Step 5: Monitor migration 

1. Use the DMS console task status.
2. Check CloudWatch logs if errors occur.
3. Wait until:
   • Full load completes
   • CDC enters “Replication ongoing” (if enabled)

You can now run basic validation queries, as in the manual section, to verify successful migration.

Limitations of Using DMS:

• Not all SQL Server features are supported by DMS. Notably, features like SQL Server Agent jobs, FILESTREAM, and Full-Text Search are not available when using this service.
• While DMS can use SQL Server CDC for replication, it doesn’t migrate the CDC setup itself to Redshift. You’ll need to implement change tracking separately in the target, if needed.
• The initial setup and configuration of DMS can be complex, especially for migrations that involve multiple source and target endpoints.

Choose the Right Migration Approach

You now have a clear understanding of how to move data from Microsoft SQL Server to Amazon Redshift, along with the strengths and trade-offs of each approach. The right method depends on your technical expertise, data volume, sync frequency, and how quickly you need reliable analytics.

If you want the simplest option, Hevo Data is a clear choice. Once the pipeline is set up, data syncs incrementally, schemas stay up to date, and scaling happens automatically as volumes grow.

Hevo enables automated data movement from SQL Server to Redshift and other modern data warehouses, so you can focus on analysis instead of pipeline maintenance. 

Find out how to move data from AWS RDS MSSQL to Redshift to improve your data analysis.Want to consult with an expert? Book a free demo today!

FAQs on MS SQL Server to Redshift