Steps to Migrate Data From Microsoft SQL Server to Snowflake in Minutes

Microsoft SQL Server is flexible and versatile and is amenable to almost all SME needs and workloads. However, there are several things that Snowflake is better at and is the reason why organizations often migrate data SQL Server to Snowflake.

Knowing SQL Server

Microsoft SQL Server is a database server and its primary function is to store and retrieve data. It is an amalgamation of both the Structured Query Language (SQL) and the Relational Database Management System (RDBMS). A data center version is available for high levels of application support and scale while a lower scaled-down version caters to freeware. Applications are supported on a local area network or across the web on a single machine and a SQL Server blends easily into the full Microsoft Ecosystem. 

Knowing Snowflake

Snowflake is a cloud-based Data Warehouse-as-a-Service (DWaaS) and runs on the popular cloud provider AWS. As in all other databases, it is possible to load and query structured relational data in the Snowflake tables through specific SQL data types like NUMBER, BOOLEAN, VARCHAR, TIMESTAMPS, and more.  

There are several benefits to migrate data SQL Server to Snowflake.

• Various types of data both structured and unstructured can be optimized and natively migrated to Snowflake including JSON, AVRO, XML, and PARQUET data.
• Snowflake offers multiple workgroups simultaneous access to multiple workloads of data without any lag in performance and roadblocks.
• Snowflake provides separate storage and computing capabilities. Users can hence scale workloads up or down as required seamlessly, paying only for the quantum of resources used.
• Being cloud-based, Snowflake architecture supports a wide range of cloud vendors, frequently adding new ones. Users are greatly benefitted as the same tools can be used to process and analyze data of different cloud vendors.
• Snowflake provides all-round support to users by auto-scaling up or down most processes from computing to encoding columns. Indexes do not have to be specified as data is automatically clustered. While working with very large tables however, users have to employ the clustering keys of Snowflake to co-locate table data.

These are some of the benefits offered by Snowflake that make businesses want to migrate data SQL Server to Snowflake.

Loading data from Microsoft SQL Server to Snowflake

There are quite a few steps to go through in the migration process. It can be done easily and in a few clicks only. However, if DBAs are not sure about the methodology, it is advisable to contact experts in the field.

• Extract data from SQL Server – The first step is to mine data from the SQL Server. For users of databases, the most preferred process is to use queries for extraction. Select statements are used to sort, filter, and limit the data while retrieving it. Microsoft SQL Server Management Studio is used to extract bulk data when exporting entire databases in CSV, text, or SQL queries formats.
• Process the extracted data – Raw data extracted from a SQL Server cannot be migrated to Snowflake unless it is prepared and processed. The data structures supported by Snowflake have to be verified to ensure that the data to be loaded matches them. For JSON or XML data, a schema need not be specified before loading.
• Upload data to a temporary location – MySQL data cannot be loaded directly into a Snowflake table, they have to be kept in a temporary location first. This process is called Staging and has two aspects.

Internal staging is created specifically with respective SQL statements. Here, users get a great degree of flexibility when they migrate data SQL Server to Snowflake as loading is made easier by allotting file formats and other options to named stages.

External staging refers to the locations supported by Snowflake, currently Amazon S3 and Microsoft Azure. The data is uploaded using the respective cloud interfaces. 

• Migrate data into Snowflake – A unit of Snowflake is the Data Loading Overview. It facilitates the process of loading data and guides users through it. This tool is mainly used for loading bulk data and large databases. For smaller databases, Snowflake’s data loading wizard may be applied. For high data loading volumes go through the following steps.

PUT command to stage files

COPY INTO table command to load processed data into an intended table

Copy from local drive or an external staging location like Amazon S3 or Microsoft Azure where the data is located

A big help when migrating data to Snowflake is that it is possible to create a virtual data warehouse that facilitates the loading activity.

Once the data is migrated to Snowflake, provision should be there for loading fresh data. It will be tedious to load the entire SQL data again. Instead, create a script that would recognize new data in the source database and use an auto-field to continually update incremental data in the target database.

The process to migrate data SQL Server to Snowflake might initially seem complicated but with the right skills, one can easily go through it.

Image Source – https://www.bryteflow.com/