Skip to main content
Feedback

Azure SQL as a target

This is a step-by-step tutorial for Setting Azure SQL as a target.

Overview

Azure SQL Database is a fully managed platform as a service database engine that handles the majority of database management tasks like upgrading, patching, backups, and monitoring without user involvement.

Prerequisites

  • You must first create a new user for Data Integration in your database, which you can do by copying the following commands:
CREATE LOGIN [ < Data_Integration Username>] WITH PASSWORD='< myPassword >'
note

Set up your password following the review of the Microsoft Password Policy.

Use the database from which you want to pull data. Replace <database> with the name of your current database:

USE [< database >]

Allow the new user you created above to access the database:

CREATE USER [< Data_Integration Username >] FOR LOGIN &lt;database&gt;

Now grant the table or schema you want to extract data from to the new user you just created. Change the <database>, <schema>, and <table> to your existingdatabse, schema, and table names:

GRANT SELECT to [< Data_Integration Username >]

Use the following syntax to grant a SELECT operation to a specific schema:

GRANT SELECT on SCHEMA :: [< schema >] TO [< Data_Integration Username >]

Grant Data Integration permissions to the metadata of the tables in order to get the correct mapping of the tables in the schema or database:

GRANT CONTROL ON [`<db-object>`] TO [< Data_Integration Username >]
GRANT ALTER ON [`<db-object>`] TO [< Data_Integration Username >]
  • If you want to connect using Azure Active Directory, make sure you configure and manage Azure AD authentication with Azure SQL.

Connection procedure

You can connect to Azure SQL using one of two methods:

  • SQL server authentication
  • Azure active directory

SQL server authentication

  1. Whitelist our IPs.
  2. Choose your Connection Name.
  3. Enter Host.
  4. Fill in the Port Number.
  5. Enter your Database.
  6. Select SQL Server Authentication method.
  7. Enter your Username and Password
  8. Use the Test Connection function to see if your connection is up to the task. If the connection succeeded, you can now use this connection in Data Integration.

Azure active directory

  1. Whitelist our IPs.
  2. Choose your Connection Name.
  3. Enter Host.
  4. Fill in the Port Number.
  5. Enter your Database.
  6. Select Azure Active Directory authentication method.
  7. Enter your Username and Password
  8. Use the Test Connection function to see if your connection is up to the task. If the connection succeeded, you can now use this connection in Data Integration.

Custom file zone

Data Integration allows you to create a custom File Zone, to manage your data in your own Azure service.

note

For Azure Blob Storage containers to work properly with Azure SQL, a SAS token must be entered in the connection form. To create a SAS token, consult the Microsoft documentation.

Once you've finished creating the container and connecting to Azure Blob storage in Data Integration, take the following actions:

  1. Toggle the Custom File Zone to true.
  2. By clicking on FileZone Connection, you can select the previously configured FileZone Connection.
  3. Choose a Default Bucket (Container) from the drop-down list.
  4. Use the Test Connection function to see if your connection is up to the task. If the connection was successful, click Save.

Configuration process

  1. Now that you've established a connection, select your Target Connection from the drop-down list.

  2. Click the curved arrow next to Schema on the right side of the row. After the refresh is complete, click the row and choose the Schema where the data will be stored.

  3. Enter the Table Prefix.

  4. Set the Loading Mode.

    info

    Loading Modes:

    Upsert Merge - Based on the keys columns you've selected, the system will match rows and decide whether to replace matching rows, keep unmatched rows, or add new ones. This mode is recommended for continuous merge runs because it does not retain duplication and creates an index for better performance. Upsert Merge includes two merge techniques:

    • Delete-Insert- Delete all data from the table, then INSERT INTO it using the column mapping's defined keys. In this mode, new indexes are inserted to the table after existing rows are deleted (when keys between the new and existing data match).
    • Merge - Use the MERGE INTO clause to perform an upsert based on the primary keys chosen for the table mapping.

    Append Only - This will append any new records while keeping all of the current records using INSERT INTO the target Table (matching on columns).

    Overwrite - This will TRUNCATE the old records and INSERT the new records in the target table.

  5. In the Additional Options, keep the Toggle set to True if you want Data Integration Metadata to be added to the target table.

    note

    When the Source is in Multi Table mode, this option becomes available.

  6. Choose a Bucket (Container) and a path for your data to land.

  7. Set the period partition timeframe for a FileZone folder. Note: You can instruct Data Integration to divide the data according to the data insertion day, the Day/Hour, or the Day/Hour/Minute. This means that Data Integration will produce data files from your sources under folders that correspond to the correct partition you've selected.

  8. Any Source (with the exception of Webhook and CDC method Sources) to target River can now send data to your Azure SQL Container.

On this Page