Migrating AWS RDS MSSQL to Snowflake: 2 Effective Methods

Your organization may choose Microsoft SQL Server (MSSQL) on AWS RDS to store its operational data because there are no upfront investments. With AWS RDS MSSQL, you only need to pay for what your organization utilizes. 

In today’s dynamic business world, achieving the maximum value from your data is crucial. To do so, you must load all the data to an analytics platform without affecting the AWS RDS MSSQL performance. 

One of the best options is to move your data from AWS RDS MSSQL to a robust analytical platform like Snowflake. This migration ensures that transactional workloads on the AWS RDS MSSQL do not disrupt analytical queries on Snowflake. 

This article provides two methods of AWS RDS MSSQL to Snowflake migration for efficient, real-time big data analysis.

Why integrate AWS RDS for Microsoft SQL Server with Snowflake?

AWS RDS MSSQL offers only 16 TB of storage capacity for each DB instance, while Snowflake provides unlimited storage capacity. When you integrate AWS RDS MSSQL with Snowflake, you do not have to worry about the storage limit issue. 

When new data is added to the Snowflake warehouse, its architecture rearranges it into internal compressed and columnar formats. Then, this optimized data is loaded into cloud storage, which can be accessed through SQL Queries using SnowSQL CLI. This efficient storage utilization ensures elastic scaling to uncover valuable insights from data in real-time without human intervention.  

AWS RDS Microsoft SQL Server: An Overview

AWS RDS Microsoft SQL Server (MSSQL) is an Amazon RDS version compatible with Microsoft SQL Server. It allows you to quickly configure, operate, and scale SQL Server deployments in AWS Cloud on demand. 

With its cost-effectiveness and flexible computing capacity, Amazon RDS MSSQL lets you deploy various SQL Server editions within minutes. It also eliminates the need to purchase separate MSSQL Server licenses by offering a “License Included” licensing model. 

During Amazon RDS MSSQL DB configuration, you can choose between Standard and Provisioned IOPS storage and pay hourly without upfront costs or long-term contracts. 

Snowflake: An Overview

Snowflake is a data warehouse-as-a-service that allows you to create virtual data warehouses and databases to store and analyze large datasets.  It is compatible with cloud-based providers, including AWS, Azure, and GCP, which enables faster data storage, processing, and analysis at a low cost.

Snowflake is known for its instant, elastic, and infinite data scaling across public clouds. Its unique platform allows for the efficient execution of the most critical data workloads using cloud-based hardware and software. 

To improve data privacy and security, Snowflake complies with HIPAA and PCI DSS and is FedRAMP-authorized.

Methods for Data Migration from AWS RDS MSSQL to Snowflake

You can smoothly convert AWS RDS MSSQL to Snowflake using Hevo Data or a method utilizing Amazon RDS console, Amazon S3, and SnowSQL CLI.

Method 1: Migrate Data from AWS RDS MSSQL to Snowflake Using Hevo Data

Hevo Data is a no-code, real-time ELT platform that helps you create automated data pipelines at a low cost based on your needs. With 150+ pre-built connectors, Hevo Data allows you to export data, transfer it to destinations, and transform it for in-depth analysis.

Get Started with Hevo for Free

Here are the key features of Hevo Data:

• Data Transformation: Hevo Data offers an analyst-friendly approach to data transformation, making analysis more efficient. You can use a Python-based transformation script or Drag-and-Drop transformation blocks to clean, prepare, and standardize data before loading it to the destination of your choice.
• Incremental Data Load: Hevo Data can instantly transfer updated data, leading to efficient use of bandwidth on both ends of the data pipeline.
• Auto Schema Mapping: The Auto Mapping feature in Hevo eliminates the tedious task of managing schemas manually. It identifies the format of incoming data and copies it to the destination schema. You can select between full or incremental mappings based on your data replication requirements.

Let’s see how to integrate AWS RDS MSSQL to Snowflake using the Hevo Data.

Step 1: Configuring AWS RDS MSSQL as Your Source Connector

Make sure you have the following prerequisites before starting the configuration.

• MS SQL Server Version 2008 or above.
• Enable Change Tracking at the database and table levels. To know more about this step, refer to Change Tracking for a Database and Change Tracking for a Table.
• Whitelisted Hevo’s IP addresses. 
• Create a Database User and Grant SELECT and VIEW Change Tracking privileges.
• You must have a Pipeline Administrator, Team Administrator, or Team Collaborator role to create the Hevo pipeline.

Here are the steps to set up the AWS RDS MSSQL Server as the source of your pipeline:

1. Sign in to your Hevo account and choose PIPELINES from the Navigation Bar.
2. In the Pipelines List View page, click the + CREATE button.
3. From the Select Source Type page, search and choose Amazon RDS SQL Server as the Source type.
4. In the Configure your Amazon RDS SQL Server Source page, specify the following details:

5. To validate the connection settings, click on the TEST CONNECTION button.
6. Submit the TEST & CONTINUE button to proceed with configuring the destination connector.

For more information about source configuration, read the AWS RDS SQL Server documentation. 

Step 2: Configuring Snowflake as Your Destination Connector

Before you get started, make sure you have these prerequisites in place:

• An active Snowflake account or create one.
• Create and configure a new role for Hevo in your Snowflake Destination using Hevo’s ready-to-use script.
• You must have either a Team Collaborator or an administrator role in Hevo, excluding the Billing Administrator role.
• Obtain the URL of your Snowflake account.

Here are the steps to set up the Snowflake as your destination end of the pipeline:

1. In the Navigation Bar, select the option DESTINATIONS.
2. Go to the Destinations List View page and click the + CREATE button.
3. Choose Snowflake as the Destination type from the Add Destination page.
4. Specify the required fields in the Configure your Snowflake Destination page.

5. To validate the connection settings, click on the TEST CONNECTION button.
6. Submit the SAVE & CONTINUE button to finish the destination configuration.

For more information about destination configuration, read Snowflake Destination Configuration in Hevo. 

SIGN UP HERE FOR A 14-DAY FREE TRIAL

Method 2: Migrate Data from AWS RDS MSSQL to Snowflake Using Amazon S3 and SnowSQL CLI

To export data from AWS RDS MSSQL to Snowflake file, you must export the data from Amazon RDS DB instances to the Amazon S3 bucket. Then, the data will be loaded into Snowflake using the SnowSQL CLI command.

Ensure the following prerequisites are met before you get started:

• An active Snowflake account or set up a new one.
• A new user for the Snowflake cluster
• An active AWS account or create a new one.
• Set up Amazon RDS DB Instance for Microsoft SQL Server.
• Connect and Load Data using SQL Server Management Studio.
• An Amazon S3 Bucket

Let’s see how to insert AWS RDS MSSQL data into Snowflake table with the following steps:

Step 1: Take a Snapshot of your AWS RDS

• From the Amazon RDS console, choose your RDS instances.
• Click on the Actions > Take snapshot and provide a name for your snapshot.
• Click the Take snapshot button. Read create RDS DB snapshot for more information.

Step 2: Enable Access to S3 Buckets

• Create an IAM role and assign a policy to allow AWS RDS to access your S3 buckets. Read allowing access to S3 Buckets to perform this step.

Step 3: Export the RDS Snapshot to S3

• Navigate to the RDS > Snapshots and choose the snapshot you wish to export.
• Click the Actions > Export to Amazon S3 option. For more information, read exporting RDS DB snapshot to S3. 
• When redirected to the Export to Amazon S3 page, specify the necessary configurations.

• Under Encryption, choose a KMS key or ARN to encrypt the snapshot. You cannot export the snapshot without a KMS key. If you do not have one, create one and configure it.
• Navigate to the RDS console and paste the ARN or KMS key.
• Finally, click the Export to Amazon S3 button, and the data from AWS RDS MSSQL will be exported to the Amazon S3 bucket in Parquet file format. 

Step 4: Loading the Data into the Snowflake Table

• Log in to your Snowflake account using the SnowSQL CLI or install it.
• Create a database where you wish to import the data using the following command:

create or replace DATABASE sample_database;

• Convert the Snowflake file format to Parquet format using the following command:

create or replace file format parquet_format type = ‘parquet’; 

• Create a destination table in Snowflake to import the data from the snapshot using the following command:

create or replace table <destination_table_name> (sales_raw variant);

• Load the data into the Snowflake destination table using the following SnowSQL CLI command:

copy into <destination_table_name> 

from s3://snowflakebucket/data/sample_database

credentials=(aws_key_id=’$KEY_ID’ aws_secret_key=’$SECRET_KEY’)

file_format = (type =’parquet’)

header = true;

Limitations of AWS RDS MSSQL to Snowflake Migration Using S3 and SnowSQL CLI

• Extensive Knowledge: For a smooth AWS RDS MSSQL to Snowflake integration, you need extensive familiarity with the AWS RDS console, Amazon S3 console, and SnowSQL CLI commands. Any issues arising during configuration will cause delays until they are resolved.
• Additional Coding: You have to write additional code to clean, prepare, and standardize the data before loading it to Snowflake.

Use Cases of AWS RDS MSSQL to Snowflake Migration

• Improved Business Workflow: Snowflake’s OLAP cube structure helps organize multiple data dimensions that you can analyze to solve a business problem. This scalable OLAP cube allows your organization to easily analyze data from different perspectives, enabling you to derive deeper insights quickly.   

Conclusion

Organizations utilize AWS RDS MSSQL’s speed and flexibility and Snowflake’s real-time analytics tools to manage large datasets effectively.

In this article, you have learned how to connect AWS RDS MSSQL to Snowflake using Hevo Data or manually through Amazon S3 and SnowSQL CLI. 

Integrating AWS RDS MSSQL with Snowflake via Amazon S3 and SnowSQL CLI requires manual file updates on both platforms, leading to slower real-time data synchronization.

Hevo Data eliminates this drawback because of its no-code, real-time automated data pipeline, which helps with quick configuration and loads updated data into the Snowflake file in real-time.

To learn more about importing data into Snowflake, read 4 Methods to Load Data into Snowflake. 

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also checkout our unbeatable pricing to choose the best plan for your organization.

Share your experience of AWS RDS MSSQL to Snowflake integration in the comments section below!

Frequently Asked Questions (FAQs)

Q. I have a 500 TB AWS RDS SQL Server Database. I want to load the data from my RDS SQL Server DB instance to Snowflake. How can I perform this data integration cheaply and in less time?

Using Microsoft SQL Server Integration Services (SSIS), you can initially load all your DB tables into the S3 bucket and then utilize external tables to migrate the data from the S3 bucket to Snowflake tables. However, this data integration process can be time-consuming, so you can opt for Hevo Data’s readily available connectors to complete the task in minutes at a low cost.