MS SQL Server to Redshift: 2 Easy Methods

With growing volumes of data, is your SQL Server getting slow for analytical queries? Are you simply migrating data from MS SQL Server to Redshift? Whatever your use case, we appreciate your smart move to transfer data from MS SQL Server to Redshift.

This article, in detail, covers the various approaches you could use to perform data replication from MS SQL Server to Redshift.

Table of Contents

• Introduction to Microsoft SQL Server
• Introduction to Amazon Redshift
• Understanding the Methods to Connect SQL Server to Redshift
  • Method 1: Using Custom ETL Scripts to Connect SQL Server to Redshift
    • Step 1: Upload Generated Text File to S3 Bucket
    • Step 2: Create Table Schema
    • Step 3: Load the Data from S3 to Redshift Using the Copy Command
    • Limitations of using Custom ETL Scripts to Connect SQL Server to Redshift
  • Method 2: Using Hevo Data to Connect SQL Server to Redshift
• Conclusion

Introduction to Microsoft SQL Server

Microsoft SQL Server is one of the world’s most popular and widely used relational database management systems. With great security, reliability, and, ease of connecting to multiple applications across a network, SQL Server is a popular choice for OLTP systems.

It supports ANSI SQL, the standard SQL (structured query language) language. SQL Server comes with its implementation of the proprietary language called T-SQL (transact SQL). SQL server management studio (SSMS) is the main interface tool, consisting of a client and server. A client is an application for sending requests to the SQL Server installed on a given machine, whereas a server is capable of processing input data as requested.

SQL Server provides enterprise-grade security and support which makes it a favorite among organizations with strict compliance requirements. 

Here are a few key features of SQL Server that make it an indispensable tool:

• The Database Engine: This segment handles data storage, fast exchange processing, and data security.
• The SQL Server: This assistance begins, pauses, stops, and continues the dataset process. 
• The Server Agent: It plays out the job of the task scheduler. It tends to be set off by any occasion or according to request. 
• The SQL Server Browser: This receives approaching queries and connects with the ideal SQL server instance. 
• The SQL Server Full-Text Search: This permits the client to run a full-text search against Character information in SQL Tables. 

Introduction to Amazon Redshift

Amazon Redshift data warehouse is an enterprise-class relational database query and management system. Amazon Redshift achieves efficient storage and optimum query performance through a combination of massively parallel processing, columnar data storage, and very efficient, targeted data compression encoding schemes. Read more about Redshift Architecture here.

Amazon Redshift is primarily designed to work with Big Data and is easily scalable due to its modular node design. It also allows users to gain more granular insight into datasets, owing to the ability of Amazon Redshift Clusters to be further divided into slices. Amazon Redshift’s multi-layered architecture allows multiple queries to be processed simultaneously thus cutting down on waiting times. Apart from these, there are a few more benefits of Amazon Redshift that are covered in the following section.

Here are a few key features of Amazon Redshift that make it an indispensable tool:

• Enhanced Scalability: Amazon Redshift is known for providing consistently fast performance, even in the face of thousands of concurrent queries. Amazon Redshift Concurrency Scaling supports nearly unlimited concurrent queries and users. By leveraging Redshift’s managed storage, capacity is added to support workloads of up to 8 PB of compressed data. Scaling is just a simple API call, or a few clicks in the console away.    
• Easy Management: Amazon Redshift automates oft-repeated maintenance tasks so that you can focus on gathering actionable insights from your data. It is fairly simple to set up and operate. A new Data Warehouse can be deployed with just a few clicks in the AWS console. Key administrative tasks like backup and replication are automated. Data in Amazon Redshift is automatically backed up to Amazon S3. Amazon Redshift can replicate your snapshots to Amazon S3 asynchronously in a different region for disaster recovery. The Automatic Table Optimization selects the best distribution keys and sort method to enhance the performance efficacy for the cluster’s workload. Amazon Redshift also gives you the flexibility to work with queries in the console, or Business Intelligence tools, libraries, and SQL client tools.  Also, check out the Redshift Sortkeys article.
• Robust Security: Amazon Redshift is known for providing robust data security features at no extra cost. Amazon Redshift allows you to configure firewall rules to take control of network access to a specific Data Warehouse Cluster. Amazon Redshift also specializes in granular column and row-level security controls that ensure that users can only view data with the right type of access. Apart from these, Amazon Redshift also delivers on its promise of reliability and compliance through tokenization, end-to-end encryption, network isolation, and auditing.  
• Data Lake and AWS Integrated: Amazon Redshift allows you to work with data in various open formats that can easily integrate with the AWS ecosystem. Amazon Redshift makes it exceptionally easy to query and write data to your Data Lake in open formats such as JSON, ORC, CSV, Avro to name a few. The federated query capability allows you to query live data across multiple Aurora PostgreSQL and Amazon RDS databases to get enhanced visibility into the business operations. This is carried out without the need for any undesired data movement. The AWS Analytics ecosystem allows you to handle end-to-end analytics workflows without any hiccups. You can also bring in data from various applications like Google Analytics, Facebook Ads, Salesforce to an Amazon Redshift Data Warehouse in a streamlined manner.     
• Flexible Performance: Amazon Redshift distinguishes itself by offering swift, industry-leading performance with a keen focus on flexibility. This is made possible through result caching, materialized views, efficient storage, RA3 instances, and high-performance query processing to name a few. Result Caching is used to deliver sub-second response times for repeat queries. Business Intelligence tools, dashboards, visualizations leveraging repeat queries experience a significant performance boost. At the time of execution, Amazon Redshift looks through the cache to see if there is a cached result for repeat queries. Amazon Redshift also uses sophisticated algorithms to classify and predict the incoming queries based on their run times and resource requirements to manage concurrency and performance dynamically. This helps users prioritize business-critical workloads.   

This article covers the steps involved in writing custom code to load data from SQL Server to Amazon Redshift. Towards the end, the blog also covers the limitations of this approach.

Understanding the Methods to Connect SQL Server to Redshift

These are the two methods you can implement to set up a connection from SQL Server to Redshift in a seamless fashion:

• Method 1: Using Custom ETL Scripts to Connect SQL Server to Redshift
• Method 2: Using Hevo Data to Connect SQL Server to Redshift

Method 1: Using Custom ETL Scripts to Connect SQL Server to Redshift

As a pre-requisite to this process, you will need to have installed Microsoft BCP command-line utility. If you have not installed it, here is the link to download it.

For demonstration, let us assume that we need to move the ‘orders’ table from the ‘sales’ schema into Redshift. This table is populated with the customer orders that are placed daily.

There might be two cases you will consider while transferring data.

1. Move data for one time into Redshift.
2. Incrementally load data into Redshift. (when the data volume is high)

Let us look at both scenarios:

One Time Load

You will need to generate the .txt  file of the required SQL server table using the BCP command as follows :

Open the command prompt and go to the below path to run the BCP command

C:Program Files <x86>Microsoft SQL ServerClient SDKODBC130ToolsBinn

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

bcp "sales.orders" out D:outorders.txt -S "ServerName" -d Demo -U UserName -P Password -c

Note: There might be several transformations required before you load this data into Redshift. Achieving this using code will become extremely hard. A tool like Hevo, which provides an easy environment to write transformations, might be the right thing for you. Here are the steps you can use in this step:

• Step 1: Upload Generated Text File to S3 Bucket
• Step 2: Create Table Schema
• Step 3: Load the Data from S3 to Redshift Using the Copy Command

Step 1: Upload Generated Text File to S3 Bucket

We can upload files from local machines to AWS using several ways. One simple way is to upload it using the file upload utility of S3. This is a more intuitive alternative.
You can also achieve this AWS CLI, which provides easy commands to upload it to the S3 bucket from the local machine.

As a pre-requisite, you will need to install and configure AWS CLI if you have not already installed and configured it. You can refer to the user guide to know more about installing AWS CLI.

Run the following command to upload the file into S3 from the local machine

aws s3 cp D:orders.txt s3://s3bucket011/orders.txt

Step 2: Create Table Schema

CREATE TABLE sales.orders (order_id INT,
  customer_id INT,
  order_status int,
  order_date DATE,
  required_date DATE,
  shipped_date DATE,
  store_id INT,
  staff_id INT
 )

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

Select * from sales.orders

Step 3: Load the Data from S3 to Redshift Using the Copy Command

COPY dev.sales.orders FROM 's3://s3bucket011/orders.txt'
iam_role 'Role_ARN' delimiter 't';

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.

Limitations of using Custom ETL Scripts to Connect SQL Server to Redshift

• In cases where data needs to be moved once or in batches only, the custom ETL script method works well. This approach becomes extremely tedious if you have to copy data from MS SQL to Redshift in real-time.
• In case you are dealing with huge amounts of data, you will need to perform incremental load. Incremental load (change data capture) becomes hard as there are additional steps that you need to follow to achieve it.
• Transforming data before you load it into Redshift will be extremely hard to achieve.
• When you write code to extract a subset of data often those scripts break as the source schema keeps changing or evolving. This can result in data loss.

The process mentioned above is frail, erroneous, and often hard to implement and maintain. This will impact the consistency and availability of your data into Amazon Redshift.

Method 2: Using Hevo Data to Connect SQL Server to Redshift

Hevo helps you directly transfer data from SQL Server and various other sources to a Data Warehouse, such as Redshift, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled securely and consistently with zero data loss.

Sign up here for a 14-Day Free Trial!

Hevo takes care of all your data preprocessing to set up SQL Server Redshift migration and lets you focus on key business activities and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. 

• Step 1: Connect to your MS SQL Server by providing the SQL Server hostname, Pipeline Name, SQL Server Port, SQL Server Username, password, database name, and schema name.

• Step 2: Select the replication mode: (a) Full Dump and Load  (b) Incremental load for append-only data (c) Incremental load for mutable data.

• Step 3: For each table in MS SQL Server choose a table name in Redshift where it should be copied. Complete the connection by providing your Redshift databases credentials like Database Port, Username, Password, Name, Schema, and Cluster Identifier along with the Destination Name.

As can be seen, you are simply required to enter the corresponding credentials to implement this fully automated data pipeline without using any code.

Check out what makes Hevo amazing:

• Real-Time Data Transfer: Hevo with its strong Integration with 100+ sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
• Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
• Connectors: Hevo supports 100+ integrations to SaaS platforms, files, databases, analytics, and BI tools. It supports various destinations including Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL databases to name a few.  
• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled securely and consistently with zero data loss.
• Tremendous Connector Availability: Hevo houses a large variety of connectors and lets you bring in data from numerous Marketing & SaaS applications, databases, etc. such as Google Analytics 4, Google Firebase, Airflow, HubSpot, Marketo, MongoDB, Oracle, Salesforce, Redshift, etc. in an integrated and analysis-ready form.
• Simplicity: Using Hevo is easy and intuitive, ensuring that your data is exported in just a few clicks. 
• Completely Managed Platform: Hevo is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Conclusion

That’s it! You are all set. Hevo will take care of fetching your data incrementally and will upload that seamlessly from MS SQL Server to Redshift via a real-time data pipeline.

Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day! 

Visit our Website to Explore Hevo

Hevo offers a faster way to move data from Databases or SaaS applications like SQL Server into your Data Warehouse like Redshift to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code. 

Sign Up for a 14-day free trial to try Hevo for free. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.