How to use the SQL Server Destination.

This destination syncs your data to your SQL Server instance in real time and in Relational Table format. Following the ELT way of loading data MovingLake will sync raw JSONs into tables with _raw suffixes. JSON tables are also appended with the time it the row was loaded as well as appending a unique identifier and a SHA-256 HASH of the raw JSON payload. These last two fields are quite important not just for locating rows but also when doing joins across related tables as it is not common for these to natively have an ID which relates them.

After syncing the raw JSONs, MovingLake automatically transforms the data into relational tables using our Automatic Transformer. These tables are named the same as the JSON tables but without the _raw suffix. Also one raw table might generate more than one transformed table.

Connector guide.

Follow the next steps to allow MovingLake to connect to your instance:

1. Create a SQL login, database and user within you SQL Server using the following commands:

USE master
CREATE LOGIN movinglake WITH PASSWORD=$mypassword

CREATE DATABASE MLDB

USE MLDB 
CREATE USER movinglakeuser FROM LOGIN movinglake WITH DEFAULT_SCHEMA=dbo;


ALTER ROLE db_owner ADD MEMBER movinglakeuser  

2. Gather all of your instance data such as:

• Host URL
• Database Name (MLDB)
• Database Port (1433)
• Schema (dbo)
• User Name (movinglake)
• User Password

2. Head over to MovingLake, create a new SQL Server credential using this data.
3. That’s it! You are ready to use your SQL Server destination.