How to Migrate from SQLServer to Redshift

Galaxy Glossary

How do I migrate a SQL Server database to Amazon Redshift without major downtime?

Move schema and data from Microsoft SQL Server to Amazon Redshift with minimal downtime.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why migrate SQL Server data to Redshift?

Gain elastic storage, columnar performance, and simplified maintenance by moving large analytical workloads from SQL Server to Amazon Redshift.

What are the high-level steps?

1) Assess objects with AWS Schema Conversion Tool (SCT). 2) Convert and apply DDL in Redshift. 3) Extract SQL Server data to Amazon S3. 4) Load data into Redshift with COPY.5) Validate and cut over.

How do I assess and convert the schema?

Install SCT, connect to SQL Server, connect to Redshift, run “Assessment Report,” then select Convert Schema & Apply to database. SCT creates compatible DISTKEY, SORTKEY, and column types.

How do I export data from SQL Server?

Use bcp or SSIS to unload each table to gzip-compressed CSV in an S3 bucket that Redshift can reach.Keep filename-table mapping consistent.

How do I bulk-load into Redshift?

Run the COPY command from Redshift, pointing to each CSV prefix, supplying IAM role, delimiter, compression, and region.

How can I keep data in sync during cutover?

Configure AWS Database Migration Service (DMS) for ongoing replication. DMS captures SQL Server CDC and applies changes to Redshift until you are ready to switch applications.

What validation queries should I run?

Count rows and compare aggregates such as SUM(total_amount) per table.Spot-check randomly sampled records for data fidelity.

Best practices for large tables?

1) Pre-create tables with proper DIST/SORT keys. 2) Split giant exports into multiple files per slice. 3) Compress CSV using gzip. 4) Use COPY’s STATUPDATE ON once, then analyze.

.

Why How to Migrate from SQLServer to Redshift is important

How to Migrate from SQLServer to Redshift Example Usage


-- Verify migrated sales volume
SELECT DATE_TRUNC('month', order_date) AS month,
       COUNT(*)                        AS orders,
       SUM(total_amount)              AS revenue
FROM   Orders
GROUP  BY 1
ORDER  BY 1;

How to Migrate from SQLServer to Redshift Syntax


-- 1. Export SQL Server table to S3
-- (run in PowerShell or cmd)
bcp "SELECT * FROM dbo.Orders" queryout Orders_0001.csv -c -t"," -S sql-prod -d shop_db -U migrator -P ******

aws s3 cp Orders_0001.csv s3://shop-migration/Orders/

-- 2. Create matching table in Redshift
CREATE TABLE Orders (
  id           INTEGER ENCODE az64,
  customer_id  INTEGER,
  order_date   DATE,
  total_amount NUMERIC(10,2)
)
DISTKEY(customer_id)
SORTKEY(order_date);

-- 3. Load data into Redshift
COPY Orders
FROM 's3://shop-migration/Orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
CSV GZIP
DELIMITER ','
IGNOREHEADER 0
DATEFORMAT 'auto';

Common Mistakes

Ignoring data type mismatches—Redshift lacks NVARCHAR(MAX) and DATETIME. Fix by letting SCT suggest VARCHAR size and using TIMESTAMP.
Loading a single huge CSV per table—creates skew and slow COPY. Export multiple files (1 per Redshift slice) and set MAXERROR.

Frequently Asked Questions (FAQs)

Can I migrate stored procedures?

Redshift lacks T-SQL procedures. Re-write logic in Python, Spectrum, or materialized views.

Is CDC mandatory?

Not for one-time loads, but AWS DMS keeps delta changes flowing until cutover, preventing data loss.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie