Documentation

Note: This article covers Azure SQL Destination setup process. This guide doesn’t cover the DataPrep setup for Azure SQL.

You can learn how to extract data from Azure SQL data source here.

Description

Azure SQL is a fully managed relational database service provided by Microsoft in the Azure cloud, offering scalability, high availability, and built-in security features for storing and managing structured data.

Follow our setup guide to connect Azure SQL to Improvado.

Database requirements

Make sure your Azure SQL database:

• is created along with the recommendations
• supports COLUMNSTORE

Permissions

You should have write access to the database and the following permissions:

• ```BULK INSERT```
• ```INSERT```
• ```CREATE```
• ```UPDATE```
• ```DELETE```
• ```ALTER INDEX```
• ```SELECT``` (for tables and data)
• Access to the system tables, like:
• ~```sys.partition_functions```
• ~```sys.partition_schemes```
• ~```sys.columns```
• ~```sys.tables```
• ~```sys.schemas```
• ~```sys.types```
• ~```sys.partition_range_values```
• ~```sys.dm_db_partition_stats```
• ~```sys.objects```
• ~it is recommended to have access to the whole ```sys``` schema
• Usage permission on the schema ```dbo```

Complete configuration

On the Azure SQL connection page, fill in the following fields:

1. Enter a name for your Destination connection in the Title.
2. Enter the Host.
3. Enter the Port.
4. Enter the UID.
5. Enter the Password.
6. Enter the Database Name. {%dropdown-button name="database-name"%}

{%dropdown-body name="database-name"%}

Database Name must satisfy the following regular expression: ```r'^[a-zA-Z@_#]([\w@$#])*$'```

{%dropdown-end%}

7. (Optional) Specify the Schema of your database.
8. Select the necessary Use static IP option from the dropdown. {%dropdown-button name="use-static-ip"%}

{%dropdown-body name="use-static-ip"%}

Select Yes for Use static IP option if you allow Improvado to connect your database by the static IPs mentioned on the Destination connection page.

Select No if you have permitted access to your database from any IP. In this case, Improvado will connect your database using dynamic IPs not listed on the Destination connection page.

{%dropdown-end%}

9. Select the necessary Partition by option from the dropdown. {%dropdown-button name="partition-by"%}

{%dropdown-body name="partition-by"%}

Partitioning is the way of splitting data for uploading to the file. Azure SQL provides two types of data partitioning:

• Daily – suitable for medium-sized datasets, good for frequent updates, but might take up extra disk space;
• Monthly – suitable for large-sized datasets, stores data on a disk in the most efficient way.

You must carefully choose your partitioning option, as you won't be able to change it in the future.

{%dropdown-end%}

10. Select the necessary Use Columnstore Index from the dropdown.
11. Select the necessary Use binary strings option from the dropdown. {%dropdown-button name="use-binary-strings"%}

{%dropdown-body name="use-binary-strings"%}

The "Use binary strings" determines whether string fields are encoded in binary format when loading data.

Recommended setting: "No" (default).

{%dropdown-end%}