Migrating AWS RDS MSSQL to Snowflake: 2 Effective Methods

Easily move your data from AWS RDS MSSQL to Snowflake to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

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.  

What is AWS RDS Microsoft SQL Server?

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. 

What is Snowflake?

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.

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.

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.

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

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.   
• Enhanced Data Analytics & BI: Migrating from AWS RDS MSSQL to Snowflake enables businesses to leverage Snowflake’s advanced analytical capabilities and scalability, driving better insights for data-driven decision-making.
• Scalability for Growing Data Needs: As data volumes grow, Snowflake’s elastic scalability allows seamless expansion, eliminating performance bottlenecks that may occur with MSSQL on AWS RDS.
• Separation of Storage and Compute: Snowflake separates storage from compute resources, offering flexibility to optimize costs and performance—ideal for companies with fluctuating workloads or seasonal data surges.

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.

Frequently Asked Questions (FAQs)