Sling logo
Sling logo
Login Docs
Sqlserver Snowflake

Migrating Data from SQL Server to Snowflake with Sling

Slinger avatar
Slinger
•
Cover for Migrating Data from SQL Server to Snowflake with Sling

Introduction

Migrating data from SQL Server to Snowflake is a common yet challenging task in modern data architecture. Organizations often face complexities in handling different data types, maintaining data integrity, and ensuring efficient transfer of large datasets. Traditional approaches involving custom scripts or ETL tools can be time-consuming and error-prone. This is where Sling comes in, offering a streamlined solution for your data migration needs.

Understanding Sling

Sling is a modern data movement and transformation platform that simplifies database migrations. It provides robust support for both SQL Server and Snowflake, handling the complexities of data type mapping, performance optimization, and monitoring. With Sling, you can:

  • Efficiently transfer data between SQL Server and Snowflake
  • Automatically handle data type conversions
  • Monitor transfer progress in real-time
  • Scale your data pipeline with ease

Installation

Getting started with Sling is straightforward. You can install it using various package managers:

# Install using Homebrew (macOS)
brew install slingdata-io/sling/sling

# Install using curl (Linux)
curl -LO 'https://github.com/slingdata-io/sling-cli/releases/latest/download/sling_linux_amd64.tar.gz' \
  && tar xf sling_linux_amd64.tar.gz \
  && rm -f sling_linux_amd64.tar.gz \
  && chmod +x sling

# Install using Scoop (Windows)
scoop bucket add sling https://github.com/slingdata-io/scoop-sling.git
scoop install sling

# Install using Python pip
pip install sling

Verify your installation by running:

# Check Sling version
sling --version

Setting Up Connections

Before migrating data, you need to configure your SQL Server and Snowflake connections. Sling provides multiple ways to manage connections securely.

SQL Server Connection

You can set up your SQL Server connection using environment variables or the sling conns set command. For detailed configuration options, see the SQL Server connection documentation.

# Set up SQL Server connection
sling conns set sqlserver_source type=sqlserver host=<host> user=<user> database=<database> password=<password> port=<port>

Snowflake Connection

Similarly, configure your Snowflake connection. For detailed configuration options, see the Snowflake connection documentation.

# Set up Snowflake connection
sling conns set snowflake_target type=snowflake account=<account> user=<user> database=<database> password=<password> role=<role>

Environment Variables

You can use environment variables for more secure credential management:

export SQLSERVER_SOURCE="sqlserver://username:password@host:port/database?options"
export SNOWFLAKE_TARGET="snowflake://username:password@account/database/schema?warehouse=compute_wh"

Environment YAML

Alternatively, you can configure your connections in the Sling environment file (~/.sling/env.yaml):

# ~/.sling/env.yaml
connections:
  sqlserver_source:
    type: sqlserver
    host: your-sqlserver-host
    port: 1433
    database: your_database
    username: your_username
    password: your_password

  snowflake_target:
    type: snowflake
    account: your-account
    user: your_username
    password: your_password
    database: your_database
    schema: your_schema
    warehouse: compute_wh
    role: your_role

Testing Connections

Before proceeding with data migration, it’s important to verify that both connections are working properly. Use the sling conns test command to test your connections:

# Test SQL Server connection
sling conns test sqlserver_source

# Test Snowflake connection
sling conns test snowflake_target

You can also discover available tables and views in your databases:

# List available tables in SQL Server
sling conns discover sqlserver_source

# List available tables in Snowflake
sling conns discover snowflake_target

Basic Data Sync with CLI

Let’s start with a simple example of migrating a single table using CLI flags:

# Sync all the tables in schema 'dbo' from SQL Server to Snowflake
sling run \
  --src-conn sqlserver_source \
  --src-stream "dbo.*" \
  --tgt-conn snowflake_target \
  --tgt-object "PUBLIC.{stream_table}" \
  --mode full-refresh

For a more complex example with multiple options:

# Sync with additional options
sling run \
  --src-conn sqlserver_source \
  --src-stream "dbo.orders" \
  --tgt-conn snowflake_target \
  --tgt-object "PUBLIC.ORDERS" \
  --mode incremental \
  --primary-key "order_id" \
  --update-key "last_modified" \
  --target-options '{"add_new_columns": true, "column_casing": "upper"}'

Advanced Data Sync with YAML

For more complex migrations, using a YAML configuration file provides better control and reusability. Here’s a basic example:

# Basic replication configuration
source: sqlserver_source
target: snowflake_target

streams:
  dbo.customers:
    object: PUBLIC.CUSTOMERS
    mode: full-refresh
    primary_key: [customer_id]
    target_options:
      add_new_columns: true
      column_casing: upper

Here’s a more complex example with multiple streams and advanced options:

# Complex replication configuration
source: sqlserver_source
target: snowflake_target

defaults:
  mode: incremental
  target_options:
    add_new_columns: true
    column_casing: upper
    table_keys:
      primary_key: [id]

streams:
  dbo.orders:
    object: PUBLIC.ORDERS
    primary_key: [order_id]
    update_key: last_modified

    # specify columns as types if needed
    columns:
      order_id: number
      customer_id: number
      order_date: timestamp
      total_amount: decimal(15,2)
    
    # select the columns
    select:
      - order_id
      - customer_id
      - order_date
      - total_amount
      - status

  dbo.order_items:
    object: PUBLIC.ORDER_ITEMS
    primary_key: [order_id, item_id]
    update_key: last_modified
    sql: |
      SELECT 
        oi.*,
        p.product_name,
        p.category
      FROM dbo.order_items oi
      JOIN dbo.products p ON oi.product_id = p.product_id

Sling Platform Overview

While the CLI is powerful for direct data operations, the Sling Platform provides a comprehensive web interface for managing your data pipelines. Key features include:

  • Visual pipeline builder
  • Real-time monitoring
  • Team collaboration
  • Automated scheduling
  • Detailed logging

Sling Platform Editor

Getting Started

To begin your SQL Server to Snowflake migration:

  1. Install Sling using your preferred method
  2. Configure your source and target connections
  3. Test your connections using sling conns test
  4. Start with a simple CLI-based migration
  5. Progress to YAML configurations for complex scenarios
  6. Consider using the Sling Platform for enterprise needs

For more detailed information, visit:

Conclusion

Sling simplifies the complex task of migrating data from SQL Server to Snowflake. Whether you’re moving a single table or implementing a complex data pipeline, Sling provides the tools and flexibility you need. Start with the CLI for simple migrations and scale up to the Platform as your needs grow.

For more examples and detailed documentation, visit docs.slingdata.io.

Back to all posts