How to Replicate Data from SQL Server to Snowflake: A Step-by-Step Guide

Data replication is key to maintaining fast, reliable access to your business insights. If you’re looking to seamlessly move data from SQL Server to Snowflake, you’re in the right place. This guide walks you through the process of setting up DBSync’s connectors, ensuring a smooth flow of data between systems. Whether you’re syncing real-time data or preparing for complex analytics, we’ve got you covered with an easy, step-by-step tutorial.

Let’s get started on optimizing your data pipeline!

Source and Target Connectors

Source and target connectors are essential in data integration and replication processes. They are key components in facilitating real-time data replication and ensuring consistent data availability across various platforms.

SQL Server

SQL Server is a powerful database system recognized for its advanced features, such as ensuring data security, high availability, and supporting business intelligence. Its layered architecture is designed to manage data safely and efficiently, providing tools to handle transactions and control concurrency when multiple users access the database simultaneously.

Snowflake

Snowflake is a cloud-based data warehousing platform designed to easily handle large volumes of data. Snowflake’s data warehousing capabilities enable organizations to store, manage, and analyze massive volumes of data efficiently and effectively.

Moving data from SQL Server to Snowflake

Replicating data from SQL Server to Snowflake is a critical strategy for organizations looking to optimize their data management capabilities. It allows for better scalability, cost efficiency, improved analytics, enhanced data governance, and disaster recovery options.

By leveraging Snowflake’s cloud-native features, businesses can unlock more value from their data and build a robust, future-ready data infrastructure.

Data Flow

SQL Server → Data Extraction 

In this initial stage, data is extracted from SQL Server using DBSync to capture the relevant tables, columns, and records. The extraction can be full or incremental based on replication needs.

Transform (ETL) → Data Transformation 

In this stage, the extracted data undergoes ETL (Extract, Transform, Load) processing outside of Snowflake. It is transformed to meet specific requirements, which may include filtering, and restructuring to align with business standards.

Staging in Snowflake → Data Loading 

Transformed data is loaded into a staging area within Snowflake, serving as a temporary storage for raw or lightly processed data. This staging area helps manage large datasets efficiently and supports incremental loads.

Validation → Data Validation and Integrity Checks 

Before final loading, data undergoes validation checks to ensure accuracy and integrity. This includes verifying completeness, checking for missing records, and identifying duplicates or inconsistencies.

Transform (ELT) → Post-Load Transformation 

In this ELT (Extract, Load, Transform) stage, data transformations occur after loading into Snowflake. This leverages Snowflake’s scalable architecture for efficient processing, including operations like aggregations, normalization, and applying business logic directly within the platform.

Final Tables in Snowflake → Data Storage 

After transformation, the data is stored in Snowflake’s permanent tables in its final, optimized form. These tables are designed for efficient querying, enabling users and systems to access the data for reporting, analytics, and departmental sharing.

Analytics & Reporting → Data Consumption 

Once stored, the data is accessible for analytics and reporting. Users can query Snowflake directly or utilize third-party tools (e.g., Power BI, Tableau) to generate insights, create dashboards, and support decision-making. This stage transforms raw data into valuable business insights.

Monitoring → Ongoing Monitoring and Maintenance

Continuous monitoring ensures smooth data processes by detecting synchronization issues, performance bottlenecks, and errors. Automated tools handle issues, while maintenance involves fixing data quality problems, re-running failed loads, and adapting transformations to maintain data reliability and system integrity.

Now that we’ve gone over how and where the data flows, let’s dive into the replication process. 

Steps to replicate data from SQL Server to Snowflake

To follow this tutorial, you’ll need an active Snowflake account and DBSync installed on your machine.

Step 1: Login to your Snowflake account or start a free trial on signup.snowflake.com

sql server to snowflake

Step 2: Install and log in to DBSync

1. Download and install the free developer edition and start your free trial.  

2. Navigate to the build directory and open the **bin** folder.  

3. For Windows users, run `startup.bat` to launch the application.  

**Path to bin folder**: `C:\DBSync_CloudCDM_V9.0.6two\bin\startup`

Then, open Chrome and visit:  

`http://localhost:8080/dbsync/`  

sql server to snowflake migration

Now, enter the login credentials you receive via email and you will be successfully logged into DBSync.

snowflake to sql server

Next, you’ll be directed to the replication dashboard. When adding an app, you’ll see various CRMs as potential sources. Choose the source that best fits your business requirements.

snowflake sql server connector

Step 3: Click on “Add App,” and select “Database” as the Data Source since we are replicating data from SQL Server to Snowflake in this tutorial. Assign a name of your choice and save the changes.

sql server to snowflake replication

Here, the name “SQLserverToSnowflake” is used, as we are replicating SQL Server data to Snowflake.

You should now see the project/profile displayed on the home screen, as shown in the example below.

sql server to snowflake

Step 4: Click on the profile name you created to access the source setup page. Select ‘SQL Server’ as the Type, then enter the required details: Host, Username, Password, Database, and Schema.

SQL Server Data to Snowflake

Click “Save” and then test the connection. If successful, a “Connection Established” pop-up window will appear.

sql server replication to snowflake

Step 5: Further click on next and setup the target page, select the Type as Snowflake and enter the required details: Host, Username, Password, Database, and Schema

For Snowflake, select “Warehouse” and enter the name of the warehouse used for computing.

migrate data from sql server to snowflake

After entering all the connection details on the Target page, click “Save” and test the connection to verify that everything is set up correctly.

snowflake connect to

Step 6: Click “Next” to navigate to the Objects page. From the left panel, select the source objects you wish to replicate to the target database. 

sql server to snowflake data type mapping

After selecting the source objects, they will be displayed under the “Selected Objects”.

connect snowflake to sql server

Now, let’s discuss the use cases and functionalities available in DBSync to replicate data from the source to the Target database.

Use Cases

connect sql server to snowflake

Under the “Run” section, you’ll find options such as “Update Schema,” “Clean Copy,” and “Source to DB (Incremental).” These allow you to update the schema, clean data from the source to the target database, or perform an incremental sync from the source to the target database.

Here is the list of functionalities depicted in the image:

snowflake sql server

I executed the Clean Copy from SQL Server and synced the data to Snowflake. You can check the progress and details on the log page.

data migration from sql server to snowflake

If data in your source database is updated and you want these changes reflected in your target database, you can perform an incremental sync. This process will update only the most recent modifications in the target database, ensuring that it stays current.

When a record’s data is changed in the source database, the last modified time will also be updated. The sync process will use this information to detect and update the modified records and update the target database.

snowflake create table with default date time sql server

When you run the Source to DB (incremental) sync, modified data will be updated from the source to the target database. You can review the status of the incremental sync on the log page.

how to connect snowflake to sql server

Next, go to the target database to verify the updated data that was modified in the source. This incremental functionality ensures that the source and target databases remain in sync.

Real-Time Replication with CDC

Real-time replication between SQL Server and Snowflake allows continuous, and instantaneous data transfer, ensuring that the data in Snowflake is always up-to-date with changes made in SQL Server.

Benefits and Capabilities of real-time replication between SQL Server and Snowflake

  • Real time data availability
  • Improved Operations
  • Cost Efficiency
  • Auto Schema
  • Low Latency
  • Change Data Capture (CDC)

Conclusion

Replicating data from SQL Server to Snowflake helps businesses scale effortlessly, cut costs, and access up-to-date data for better analytics. It centralizes data from different sources, breaks down silos, and supports real-time insights. Replicating SQLserver data to Snowflake with DBSync is a cost-effective solution that also strengthens disaster recovery and business

FAQs

How to generate database schema during replication? 

DBSync analyzes both database schemas and ensures they match; if discrepancies are found, it auto-generates the necessary schema to align them. During replication, DBSync captures and replicates the source schema to the destination automatically. Additionally, custom scripts can be implemented for more specific schema generation needs.

Can SQL Server connect to Snowflake?

Yes, SQL Server can connect to Snowflake using ODBC/JDBC drivers, Linked Server configurations, and ETL tools like DBSync. These methods enable data transfer and querying between the two systems, facilitating integration.

What cannot be replicated in Snowflake?

In Snowflake, certain SQL Server features can’t be replicated directly, like stored procedures, triggers, and custom server-side logic. Also, some data types (e.g., SQL Server’s XML or certain proprietary types) might not have direct equivalents in Snowflake, requiring transformation.

How to convert SQL query to Snowflake query?

To convert a SQL Server query to Snowflake, replace SQL Server-specific functions, adjust data types, and adapt any T-SQL syntax to Snowflake’s standard SQL. Then, test and tweak as needed for smooth execution.

One Step Away...

Get access to All our Demo and Tutorial videos, Case studies, White Papers and Webinar recordings - 50+ hours of content