Adverity can transfer data to a Microsoft SQL database via two separate methods:
- Recommended: Using the custom Microsoft SQL Server Destination.
- This method requires a Database Master Key to be created within your MS SQL Database, and allows data import via Bulk Insert.
- Using the generic SQL Database Destination.
- This method does not require a Database Master Key, but data processing will be significantly slower as tables are processed row-by-row.
CREATE A MICROSOFT SQL DESTINATION
PRE-REQUISITE - MICROSOFT SQL DATABASE MASTER KEY
- Instructions for creating a Database Master Key in Microsoft SQL can be found here.
WITHIN ADVERITY PLATFORM
- Navigate to Transfer > Destinations > +Add.
- Choose Microsoft SQL Server from the list of Destinations.
- Select an existing Microsoft SQL Connection, or create a new one using your MS SQL authorization credentials.
- Configure your Destination (see below).
- Please see our SQL Database article for further guidance on universal SQL configuration options.
- Each Datastream will automatically generate a separate table in the database (see SQL Database).
- Assign the Destination to a Workspace.
CREATE AN SQL DATABASE DESTINATION
- Please see our SQL Database article for instructions on setup and configuration of a generic SQL Database Destination.
The Workspace in which you want this Destination to reside.
The Connection authorized to access your Microsoft SQL server.
- Azure Storage:
Microsoft SQL Destinations require Azure Storage in order for the Bulk Insert to function. Please see this article for instructions on setting up Azure storage on your Workspace.
- Master Key Exists:
This field checks for the presence of the pre-requisite key, and displays a green tick to show that it has been detected.
- Table Name Template:
Set the naming convention for each newly generated table, using any combination of fixed alphanumeric characters and the listed placeholder values.
- Load state table:
If ticked, a separate table will be created and updated with all historical load operation data.
- Name of load state table:
Name of the load state table above.