Question 1

Why land warehouse data in SQL Server?

Accepted Answer

Usually because the consumers are already there. Established stored procedures, scheduled reports and a business intelligence tool pointed at SQL Server represent real investment, and repointing all of them is a larger project than delivering a modelled table where they already look. Modelling happens once in Redshift and the reporting layer stays exactly where it is.

Question 2

What if neither system is publicly reachable?

Accepted Answer

That is a common shape for this pairing and both sides handle it. A Redshift cluster inside a private VPC can be connected over an SSH tunnel, and a SQL Server instance that is not publicly exposed can be reached the same way, each through a bastion host with its own port, user and key. Worth sorting out with whoever manages the network before setup, since it is the usual cause of a stalled first connection.

Question 3

How is the data loaded?

Accepted Answer

Bulk table replication stages rows into a scratch table and merges into the destination, keeping the visible table consistent while a load runs. Model syncs write directly, batching inserts and issuing updates per row, with failed batches retried individually so errors attach to specific records. Tables and columns are created for you, and an incompatible type change triggers a migration rather than a silently wrong value.

Question 4

Can reads be incremental?

Accepted Answer

Yes, using a tracking field such as an updated timestamp, with the first run full and later runs resuming from the saved position. Two Redshift specifics matter. A primary key is required for incremental identity and merging, and because Redshift key metadata is not detected automatically you will usually need to nominate it yourself, otherwise runs fall back to full scans. And since sync queries share cluster compute, putting the connecting user in an appropriate workload management queue is worth doing. Deletes are not detected by tracking field reads.

Question 5

How do I connect Redshift to Microsoft SQL Server?

Accepted Answer

Connect Redshift with cluster details, credentials and an S3 staging bucket, and connect SQL Server with host, port, database and credentials, adding an SSH tunnel on either side if the instance is not publicly reachable. Then model the data in Redshift, map it onto the destination table, and choose whether the sync replicates tables or writes a modelled result. Destination tables and columns are created for you.

Connect Redshift with Microsoft SQL Server

Connect Redshift and Microsoft SQL Server with Polytomic

Private on both ends

Staged then merged

Custom SQL as the source

Trusted by businesses worldwide

Step-by-step integration guide

Connect Redshift in Polytomic

Connect Microsoft SQL Server in Polytomic

Create a sync between Redshift and Microsoft SQL Server

Map fields and enable the sync

Frequently asked questions

The sync and ETL platform that does it all

Powerful data sync

Advanced analytics

Enterprise security

Easy setup

Ready to connect Redshift with Microsoft SQL Server?