SQL Server to Snowflake – in 4 easy steps

SQL Server to Snowflake in 4 Easy Steps (No Coding)

Interested in knowing how to migrate from SQL Server to Snowflake? This blog gives an overview of why replicating data from SQL Server to Snowflake is a good idea and how to use BryteFlow to load data from SQL Server to Snowflake in 4 simple steps.

Quick Links

Planning a SQL Server to Snowflake migration or replication?

If you need to load SQL Server data to Snowflake we will show you one of the easiest ways to do so. BryteFlow’s replication of SQL Server data is completely automated, no-code and real-time. But first let’s examine why it may be worth your while to migrate from SQL Server to Snowflake. 6 reasons to automate your Data Pipeline

Benefits of moving data to Snowflake

  • Snowflake users do not need to worry about infrastructure and installation. Snowflake Cloud is a completely managed solution and delivered as Data Warehouse-as-a-Service (DWaaS).
  • It has pay-as-you-use pricing and separation of storage and compute so you can cut down costs of computational workloads, running queries only on data that is needed.
  • You can easily share data internally within the company and externally with clients or vendors.
  • No hardware provisioning is required, you can scale up compute easily when needed.
  • You can have multiple copies of databases, schema and tables, without needing to actually copy the data which can reduce data storage costs and save on copying time.
  • Snowflake is an analytics database and Snowflake tables can be loaded and queried using standard SQL data types e.g. BOOLEAN, VARCHAR, NUMBER, TIMESTAMPS, etc.
  • Snowflake offers almost infinitely scalable compute and is ideal if you have huge datasets and need for high concurrency. Build a Data Lake / Data Warehouse on Snowflake
  • All types of data – structured, semi-structured and unstructured can be stored in Snowflake.
  • Snowflake is versatile and runs on all three main Cloud service providers: AWS, Azure, and GCP so it can fit into any cloud migration strategy.

Comparing SQL Server and Snowflake

As you can see Snowflake is a highly, secure automated cloud data warehouse with almost infinite computational power that does not require installation of hardware or software. Snowflake’s big data architecture is shared and multi-cluster with each cluster accessing data and running independently without conflict. This is ideal for running large queries and operations simultaneously. Learn how to cut costs on Snowflake by 30%

SQL server on the other hand is a relational database, an OLTP with different editions and versions that though versatile, isn’t as flexible and responsive in handling workload increases. There is a common pool of compute resources that is shared by all SQL server processes in contrast to Snowflake where you can separate different use cases into different compute buckets. Snowflake has been built ground up for the Cloud and to handle big data requirements. It is easily scalable with compute power that can be scaled up or down as needed. Though SQL Server may offer Server virtualization and Azure SQL Server scaling is available, the procedure could be slower and may incur downtime.  SQL Server can slow down if handling multiple BI tasks since they will compete for the same resources.

SQL Server to Snowflake Migration tools, some popular choices

There are quite a few ETL tools that can help in loading data from SQL Server to Snowflake. Some are cloud native like Azure Data Factory from Azure and AWS DMS (AWS Data Migration Service) from AWS, some are legacy tools such as SSIS (SQL Server Integration Services) which is a component of Microsoft SQL Server and functions as a data migration and warehousing tool.  All these tools require significant coding and are not fully automated. In addition, there are third-party enterprise data integration tools like BryteFlow which provide no-code, real-time replication of data from SQL Server to Snowflake. Learn about BryteFlow’s ETL for Snowflake

The easiest way to get data from SQL Server to Snowflake is with BryteFlow

BryteFlow is a suite of automated, self-service data integration software that is completely codeless and extremely fast.  The easy-to-use user interface lets you connect to your SQL Server data sources and replicate data to Snowflake continually with log-based Change Data Capture, after the initial data sync. This ensures your data is always updated and in sync with data at source. You also get automated data reconciliation to ensure your data is complete. BryteFlow supports bulk loading with automated multi-threaded parallel loading, partitioning and compression. There is no coding for any process- data replication, data merges, SCD Type2 history, and DDL (Data Definition Language) are completely automated. Learn about SQL Server CDC for real-time SQL Server replication

Ingesting data from SQL Server to Snowflake step by step

Use the BryteFlow Ingest software and follow these steps:
Please ensure that all prerequisites have been met and all security and firewalls have been opened between all the components.
Set up the source connection details, assuming the source is SQL Server.

Set up the source connection details. The source can be on the cloud or on-premises.

 

Set up destination as Snowflake using your preferred choice:

  1. Load to Snowflake direct
  2. Load to Snowflake using S3

Set up the table(s) to be replicated, select the primary key and the transfer type. Primary key with history automatically keeps SCD Type2 history on Snowflake. Primary key transfer type keeps a mirror of the source, without history.

 

Schedule your data replication time and get your data flowing to Snowflake near real-time.

 

Initial sync for really large tables

Use the BryteFlow XL-Ingest software and follow these steps:

The tables can be any size, as terabytes of data can be brought across efficiently.
Configure the large table to be brought across.

Monitor the process, as the table is brought across. The table is replicated on Snowflake and is ready to use with SCD Type2 history, if configured. Overall status is displayed on the dashboard.

To experience BryteFlow with Snowflake, start with the free trial and a free training course for setting your replication. Contact us for a Demo

Learn about BryteFlow’s SQL Server Change Data Capture for real-time SQL Server Replication
Learn about BryteFlow’s SQL Server Change Tracking for real-time SQL Server Replication