Skip to main content
Feedback

Azure SQL as a target

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 create a new user for Data Integration in your database 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 >]

Permit the new user you created 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 existing database, 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 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.

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. Click Test Connection to verify your connection is up to the task. If the connection succeeds, you can 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. Click Test Connection to verify your connection is up to the task. If the connection succeeds, you can use this connection in Data Integration.

Custom file zone

Data Integration lets you 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, you must enter a SAS token in the connection form. To create a SAS token, refer to the Microsoft documentation.

  1. Select the Custom File Zone toggle.
  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. Click Test Connection to verify your connection is up to the task.
  5. If the connection succeeds, click Save.

Configuration process

  1. After establishing 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, 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 column selected, the system matches 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 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 appends any new records while keeping all the current records using INSERT INTO the target Table (matching on columns).

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

  5. In the Additional Options, keep the Toggle 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 time frame for a FileZone folder. Note: You can enable 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 produces 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