Syncing data from SQL server virtual machine on azure to snowflake using Fivetran

Description:

                     It is used to replicate data present in SQL server hosted on virtual machine database to destination (snowflake) via Fivetran 

 STEP1: Create SQL Server 2017 on a Windows virtual machine in the Azure                          portal

·       Sign in to the Azure portal using your account.

·       Select All services, then type SQL Virtual machines  in the search box as shown in below figure.

·       Select +Add to open the Select SQL deployment option page.

·       Select the Free SQL Server License: SQL Server 2017 Developer on Windows Server 2016 image from the dropdown.

·       Select create

·       Create a virtual machine

·       On the SQL Server settings tab, configure the following options:

Ø  Under Security & Networking, select Public (Internet) for SQL Connectivity and change the port to 1433.

Ø  Under SQL Authentication, select Enable. The SQL login credentials are set to the same user name and password that you configured for the VM.

STEP2: Connect to the Database Engine using SQL Server virtual machine on                        Azure

1.     On a computer connected to the internet, open SQL Server Management Studio (SSMS).

2.     In the Connect to Server or Connect to Database Engine dialog box, edit the Server name value. Enter the IP address of the virtual machine You can also add a comma and provide SQL Server's TCP port as 1433 .

3.     In the Authentication box, select SQL Server Authentication.

4.     In the Login box, type the name of a valid SQL login.

5.     In the Password box, type the password of the login.

6.     Click Connect.

7.     After connected to the SQL server we are able to see the table data which is present in database as shown in below figure

8.     Now, we have to load all the data which are present in SQL Server  into snowflake through Fivetran for that first we need to Enable change tracking at the database level  in SQL Server using the queries

              ALTER DATABASE Azure_sql SET CHANGE_TRACKING = ON

              ALTER TABLE Azure_sql.dbo.test ENABLE CHANGE_TRACKING;

STEP3:

·       Create SQL connector in Fivetran and configure it with relevant credentials

·       Now Save & Test.  After save & Test we need to select which table we want to sync into destination if we have to sync all tables we can select all the tables  in the schema

            STEP4: SNOWFLAKE

                 ·       Create a snowflake account as follows:-

                    ·       Snowflake connection creation

                    ·       Start free trail of snowflake from Add-on-Connectors in administrator

                    ·       Now in Google chrome search for snowflake and click on snowflake: your cloud data                                 platform and then click on start for free

·       Provide valid credentials to continue and then select type like enterprise  and select relevant cloud  platform AWS or azure …

 

In snowflake create warehouse and database and in worksheet run the following script:-

begin;

-- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)

set role_name = 'PC_FIVETRAN_ROLE';

set user_name = 'PC_FIVETRAN_Azure';

set user_password = '';

set warehouse_name = 'PC_FIVETRAN_WAREHOUSE';

set database_name = 'PC_FIVETRAN_DB';

-- change role to securityadmin for user / role steps

use role securityadmin;

-- create role for fivetran

create role if not exists identifier('$PC_FIVETRAN_ROLE');

grant role identifier('$PC_FIVETRAN_ROLE') to role SYSADMIN;

-- create a user for fivetran

create user if not exists identifier('PC_FIVETRAN_Azure')

password = ''

default_role = '$PC_FIVETRAN_ROLE'

default_warehouse = 'PC_FIVETRAN_WAREHOUSE';

grant role identifier('$PC_FIVETRAN_ROLE') to user identifier('PC_FIVETRAN_Azure');

-- change role to sysadmin for warehouse / database steps

use role sysadmin;

-- create a warehouse for fivetran

create warehouse if not exists identifier('$PC_FIVETRAN_WAREHOUSE')

warehouse_size = small

warehouse_type = standard

auto_suspend = 60

auto_resume = true

initially_suspended = true;

-- create database for fivetran

create database if not exists identifier('$PC_FIVETRAN_DB');

-- grant fivetran role access to warehouse

grant all privileges

on warehouse identifier('PC_FIVETRAN_WAREHOUSE')

to role identifier('$PC_FIVETRAN_ROLE');

-- grant fivetran access to database

grant all privileges

on database identifier('PC_FIVETRAN_DB')

to role identifier('$PC_FIVETRAN_ROLE');

 commit;

 

 

STEP4:

After that provide credentials as below:-

Ø   HOST:- Ouraccount.snowflakecomputing.com(from URL)

Ø   PORT:- 443 (default)

Ø   USER:- PC_FIVETRAN_USERNAME

Ø   DATABASE:- PC_FIVETRAN_DB

Ø   AUTH:- PASSWORD/LICENSE KEY

Ø   PASSWORD:- Snowflake LOGIN password

Ø   DATA PROCESSING LOCATION:- Asia-Pacific

Ø   TIMEZONE:- AST (UTC -04)

After save and test the data synced  to snowflake as follows

 

               STEP5: Insert a new record in SQL server and load into snowflake

·        After  insertion data in SQL server

·        Now, Data synced to snowflake through Fivetran  by setting sync frequency to 5 min .if we put 5 min sync frequency . Every 5 min the data will updated to your destination

STEP6: Update  a  record in SQL server and load into snowflake

·       Update the record in sql server using these query

UPDATE test

SET city='HYDERABAD'

WHERE name='sanjeev'

·       Now record updated city from Kolhapur to Hyderabad in SQL as shown in below figure.

 

·        Now, Data synced to snowflake through Fivetran