Replicating data from Microsoft SQL Server to Redshift with Airbyte provides faster analytical queries. Data analytics need dedicated compute resources. When processing terabytes to petabytes of data for the purpose of analytics, SQL server may be slow and expensive due to its per socket, CPU-based billing model. Moving to Redshift significantly reduces processing time and running costs.

Airbyte Cloud allows you to seamlessly move data between any data source and destination, including popular databases, data warehouses, and business applications. Airbyte's database replication to datawarehouses uses change data capture (CDC) with checkpointing capabilities and scheduling to simply pick up from where you left off.

Airbyte is like a data engineer's secret weapon! With its powerful capabilities, you can also set up various Data Integrations including Postgres to Redshift and Postgres to BigQuery, among many other connections. It's the perfect tool to supercharge your data engineering projects and make them shine!

This tutorial will take you through the critical steps to set up Airbyte Cloud and replicate data from your SQL Server instance running in Amazon RDS to Redshift.
‍

{{COMPONENT_CTA}}

Prerequisites

Below are the prerequisite tools you’ll need to get started on replicating up your SQL Server data to Redshift.

1. You’ll need to get an Airbyte Cloud account to replicate the data. You can sign up for Airbyte Cloud here.
2. You will need an instance of SQL Server that you can connect to remotely. You can get a hosted SQL Server instance through Amazon RDS at the link here.
3. You will also need an instance of Amazon Redshift that you can connect to remotely. You can get started with Amazon Redshift by creating an AWS account at the link here.

Methods to Move Data From Microsoft sql server to redshift

• Method 1: Connecting Microsoft sql server to redshift using Airbyte.
• Method 2: Connecting Microsoft sql server to redshift manually.

Method 1: Connecting Microsoft sql server to redshift using Airbyte

Step 1: Set up SQL Server as the Airbyte source

In this example, we will configure an existing SQL Server instance hosted on AWS as our Airbyte Cloud Source. Log into AWS and go to Amazon RDS > > Connectivity and Security to make note of your endpoint and port that will be required to configure Airbyte.

Note: Ensure that the DB instance's public accessibility is set to Yes to allow external connections. To modify the Public access setting, see Modifying an Amazon RDS DB instance.

Next, we will need to update the security group just created by going to EC2 > Security Groups > Create security group. Give the group a name and description. In the Inbound Rules section, select MSSQL as the type and add ‘34.106.109.131/32’ (Airbyte Cloud IP) in the Source section and add the rule. Next, add another rule with MSSQL type and choose ‘My IP’ from the drop-down. This will allow you to connect to your instance from your local machine.

Once the security group has been updated, you can add some data to your instance. You can use any database management tool you prefer. Now that you have your SQL Server instance set up to allow connections from Airbyte Cloud,  you can begin configuring the Airbyte Source. Login and create a new connection and select Microsoft SQL Server as the source type and give it a name. You can find more information about the SQL Server Airbyte connector at the link here. Enter the host, the port, the database name (airbyte in this case), and the user and password you used when setting up SQL Server. Once configured, click on set up the destination.

‍

Step 2: Set up Redshift as the Airbyte destination

To set up Redshift as your Airbyte destination you will have to allow connections from Airbyte Cloud to your cluster. Login to AWS and go to Amazon Redshift > Clusters > and make a note of the endpoint for your cluster.

‍

Your endpoint will be in the format :/. Make a note of these three values required to configure the Airbyte Cloud.

Next, you will also need to edit the inbound rules for the security group for your Redshift cluster. You can find the security group for your Redshift cluster in the Network and security settings section.  

‍

Once you know which security group is being used by Redshift, go back to EC2 > Security Groups > . In the Inbound Rules section, select Redshift as the type and add ‘34.106.109.131/32’ (Airbyte Cloud IP) in the Source section and add the rule.

‍

Next, from the Redshift cluster page, go to Actions > Modify publicly accessible settings.

‍

In the pop-up, select the Enable option and save changes. You can confirm that your cluster is publicly accessible by going to Properties > Network and security settings which should now be listed as Enabled.

‍

To set up your destination,  select Redshift as your destination type and give it a name. You can find more information about the Redshift Airbyte connector at the link here. Enter the host, the port, and the database name (dev in this case), and also enter the user and password you used when creating your Redshift cluster and click on set up  Destination.

‍

‍

Step 3: Set up a SQL Server to Redshift connection

Once the source and destination are configured, you can access your connection settings.  You can set the Replication frequency depending on how often you want Airbyte to replicate your data.

‍

Next, you can choose which tables to sync and set the sync frequency and the sync mode for each table individually. This example will select the customers table and set the Sync more to Incremental | Append.

You can also choose between using Raw Data or Basic Normalization. We will select Basic Normalization to set up the connection in this example. You can also choose to apply custom data transformations, but we will keep it simple by skipping the data transformation part in this example.

Once configured,  save the connection and select Sync now to run your first sync once configured. Once the sync is complete, you should see how many rows were replicated (849 in this case).

‍

To view the replicated data, go to the Redshift Query editor for your cluster and select your database to view the tables created by Airbyte Cloud.

‍

Clicking on a particular table will show you the schema generated by Airbyte for your data.

‍

You can run the following query from the query pane to view your data.

‍

You can also view the row count for your table by running the following query.

‍

To test out the incremental sync, you can add some more rows to your SQL Server table. In this example, 10 more rows were added. Once you add some more data you can run another sync. The 10 newly added rows are replicated up to Redshift.

Conclusion

To summarize, we look at how we can replicate data from SQL Server to Redshift using Airbyte Cloud by:

1. Configuring a SQL Server Airbyte Cloud source.
2. Configuring a Redshift Airbyte Cloud destination.
3. Creating an Airbyte connection that automatically replicates data from SQL Server to Redshift.
4. Incrementally syncing SQL Server data to Redshift.

We know that development and operations teams working on fast-moving projects with tight timelines need quick answers to their questions from developers actively developing Airbyte. They also want to share their learnings with experienced community members who have "been there and done that." Join the conversation at Airbyte's community Slack Channel to share your ideas with over 1000 data engineers and help make everyone's project successful.

Method 2: Connecting Microsoft sql server to redshift manually

Moving data from Microsoft SQL Server to Amazon Redshift without using third-party connectors or integrations involves several steps, including exporting data from SQL Server, preparing the data for Redshift, uploading the data to Amazon S3, and finally copying the data into Redshift. Below is a detailed step-by-step guide to accomplish this:

1. Prepare Your Redshift Cluster

Before you begin, ensure that you have an Amazon Redshift cluster up and running. You'll need to:

- Create an IAM role with S3 access.

- Attach the IAM role to your Redshift cluster.

- Open the necessary inbound rules in your Redshift cluster's security group to allow connections from your local environment.

2. Export Data from Microsoft SQL Server

#2.1. Choose Tables for Export

Decide which tables or data you want to export from SQL Server.

#2.2. Export Data to Flat Files

Use the SQL Server Management Studio (SSMS) or a command-line tool like BCP (Bulk Copy Program) to export the data to flat files (CSV format is commonly used).

Here's an example of using BCP to export a table:

```sh

bcp YourDatabase.dbo.YourTable out C:\path\to\your\exportedfile.csv -c -t, -S YourSqlServerName -U YourUsername -P YourPassword

```

#2.3. Ensure Data Types Compatibility

Make sure that the data types in SQL Server are compatible with Redshift data types. You may need to convert certain data types during the export process.

3. Prepare the Data Files

#3.1. Cleanse the Data

Ensure that the data is clean and conforms to Redshift's requirements. This might include:

- Removing or escaping special characters that might conflict with CSV formatting.

- Converting date and time formats to those supported by Redshift.

#3.2. Split Large Files

If your data files are large, consider splitting them into smaller chunks to optimize the upload and copy process.

4. Upload Data to Amazon S3

#4.1. Create an S3 Bucket

Create an S3 bucket in the AWS Management Console if you don't already have one.

#4.2. Upload Files to S3

Use the AWS Command Line Interface (CLI) or the S3 Management Console to upload your CSV files to the S3 bucket. Here's an example using the AWS CLI:

```sh

aws s3 cp C:\path\to\your\exportedfile.csv s3://your-s3-bucket-name/

```

5. Prepare Your Redshift Database

#5.1. Create Schemas and Tables

Make sure that the target schema and tables are created in Redshift to match the structure of the SQL Server data. Use the Redshift Query Editor or a SQL client to execute the necessary DDL statements.

6. Copy Data from S3 to Redshift

#6.1. Run the COPY Command

Use the Redshift COPY command to load data from the S3 bucket into the Redshift table. You'll need to provide the IAM role ARN that has access to S3:

```sql

copy your_schema.your_table

from 's3://your-s3-bucket-name/your-exportedfile.csv'

iam_role 'arn:aws:iam::your-account-id:role/your-iam-role'

csv;

```

#6.2. Monitor the Load Process

Monitor the load process for any errors or issues. You can query the `STL_LOAD_ERRORS` system table to review any errors that occurred during the COPY operation.

7. Verify Data Integrity

After the data has been loaded into Redshift, run some queries to verify that the data integrity has been maintained. Check the counts, sample data, and ensure that the data types have been correctly interpreted.

8. Optimize Performance

Once the data is in Redshift, you may need to:

- Analyze the tables to update the query planner's statistics.

- Apply sort keys and distribution keys to optimize query performance.

9. Clean Up

After the data migration is complete, remember to:

- Delete the temporary files from your local environment.

- Remove the uploaded files from the S3 bucket if they are no longer needed.

This guide provides a high-level overview of the steps required to move data from Microsoft SQL Server to Amazon Redshift without third-party tools. It's important to note that this process can be complex and may require additional steps or modifications based on the specifics of your data and environment. Always test the migration process with a subset of your data before proceeding with the full dataset.