SQLMDX - MS SQL Migrate - Using Native App

We are ready to use the Snowflake Native App and migrate a database from MS SQL to Snowflake.

SQL Migrate MS SQL Prerequisites are complete.
SQL Migrate Local Client Code is downloaded.

Visual Studio Code is running.

Using Client Code msql_pull_then_push.py

Target Database = "AdventureWorks2019"
Target Host = "TESTSQL" (Enter your MSSQL Instance)
Target Port = "1433" (Adjust to your MSSQL Instance)

Two authentication paths . Windows Auth and SQL Auth. We are passing SQL Auth Username & Password.

Set Snowflake Credentials

To be sucessful all parameters in snowflake_config need to be set.

Set Local Work Path

Choose where you want the metadata to be saved locally.

Execute the script.

I am ignoring the "Bad Owner or Permissions" warning on the .toml file. I don't recommend you do the same. It doesn't prevent the script from executing.

Metadata Saved Locally

In the folder you designated the metadata from the source has been saved. They are standard JSON file which is just a simple text file that you can open in VS Code to review.

These files will be sent to the Snowflake Native Application Stage.

Snowflake User Interface

In the Snowflake UI:

Use the Native Application Database

List the files in the Stage. LIST @_METADATA.TO_APP;

A stage is a file location where the script put the source metadata for you to injest with the Native Application.

Snowflake Create Role

If you haven't already done so when installing the Native Application you should create a Snowflake role with minimal rights to use the App. Here it is being done with the ACCOUNTADMIN role.

CREATE ROLE MIGRATEUSER;
GRANT APPLICATION ROLE MIGRATESQLAPP.APP_ROLE TO ROLE MIGRATEUSER;
CREATE DATABASE ADVENTUREWORKS2019;
GRANT ALL ON DATABASE ADVENTUREWORKS2019 TO ROLE MIGRATEUSER;
GRANT USAGE ON WAREHOUSE COMPUTE_XS TO ROLE MIGRATEUSER;
GRANT ROLE MIGRATEUSER TO USER SNO____3;
USE ROLE MIGRATEUSER;

Where you see MIGRATEUSER replace with the role name you chose.
Where you see MIGRATESQLAPP replace with the database name you created
when installing the Snowflake Native Application.
Replace COMPUTE_XS with your warehouse name.
Replace SNO____3 with your usename.

Database Preview

This is a preview of the Databases AdventureWorks2019 (our target) and the Native Application Database. You will see only the standard schemas when they were created. There are no schemas from AdventureWorks2019 in either.

Execute the main Stored Procedure

CALL _CODE.MSSQL_MIGRATE_EXTENDED('TESTSQL', 'AdventureWorks2019', FALSE, FALSE)

TESTSQL is the source server.
AdventureWorks2019 is the source database.
FALSE Double Quote Identifiers. (see below)
FALSE Convert Views To Tables.

Double Quote Identifiers

MS SQL and Snowflake have different rules and reserved words. When the Native App encounters one of these you have two choices. If you double quote the identifier it will come over exact. If you don't double quote the identifier then all identifiers will be uppercased and special characters will be underscored. If you use quoted identifiers then everytime they appear in a clause you must use double quotes. Some examples below.

Source Column Name in MS SQL

Column Name in Snowflake Quoted Identifiers = TRUE

Column Name in Snowflake Quoted Identifiers = FALSE

LASTNAME

No change necessary.

FirstName

"FirstName"

FIRSTNAME

To preserve lower case characters you have to double quote.

ACCOUNT

"ACCOUNT"

_ACCOUNT

Account is Snowflake reserved word. Underscore makes it valid.

SOME#NAME

"SOME#NAME"

SOME_NAME

# is an invalid character in an Identifier. Underscore makes it valid.

Application Complete

5 min execution time
X-Small Compute

7 Schemas
71 Tables
21 Views
486 Columns
91 Foreign Keys
360 Indexes

_METADATA DDL Scripts

In the schema _METADATA are tables that contain the DDL of your schema. You can select it directly to create any deployment mechanisms you wish.

_METADATA DDL Scripts

You can select the tables created by the application.

Snowflake Security

The SCHEMA HumanResources has come over from AdventureWorks2019.

You can not view the DDL of that table even though it is a valid table because it exists in the Native Application. This is by design. We give you a script to recreate all of these objects into your own database where you own all the objects and can view the DDL directly.

mssql_create_snowobjects.sql

AdventureWorks2019 in Snowflake

Your role owns the entire schema.
You can GET_DDL on all objects.
Here the previously blocked DDL is now fully available.

AdventureWorks2019 Table Ready To Load Data

Columns correctly defined.
Primary Key defined.
Full DDL available.

GET_DDL Example

Specifiy the target and name and the DDL is created.
The schema that was scripted in this example can be found here.

Sample Schema Scripted in Snowflake