RDBMS providers: Azure

Overview

Redpoint Data Management v9.6.1 introduces a next-generation set of database providers, including Azure Synapse (with bulk load and bulk query support) and Azure SQL (with bulk load). These next-generation data providers were rewritten to be more robust and performant.

• Next-generation provider features: Refer to RDBMS database providers for additional information.

• Connection options: Refer to Configure RDBMS connection options for information about setting options for both next-generation and legacy database providers.

• Schemas: Refer to RDBMS schemas for information about schemas for both next-generation and legacy database providers.

Next-generation database providers

NEW IN 9.6.1

Using bulk load and bulk query in Azure Synapse

A new provider parameter, Temp folder, has been added for Azure Synapse (under the Source tab, there’s a list of connection attributes that can be set at the data connection level or at the individual RDBMS tool level). This optional parameter is used for the Azure Synapse bulk inserter. It is a cloud storage location in which to store temporary files. The location can be a container in either ADL2 or ABS, or it can be a subdirectory in that container. If this parameter is not provided, RPDM will create a container for these temporary files in the first storage account it finds.

If the Temp folder property is set, this will take priority over any heuristic search for a suitable cloud storage temp folder.

If there is no Temp folder specified, the database provider will look for a suitable folder as follows:

1. Search adl2:/// first, then abs:///.

2. Search each defined storage account.

3. If a container exists in that account named rpdm10-azuredw-upload, use it.

4. If the rpdm10-azuredw-upload container doesn’t exist, RPDM will attempt to create the container, provided that it has the required Azure Storage permissions to create a container.

5. If no matching account/container is found, an error is thrown.

So, creating the container rpdm10-azuredw-upload in the first storage account location will make the default (scheme:///account/rpdm10-azuredw-upload) work.

• When using bulk load and bulk query in Azure Synapse, you need to define cloud storage in /Settings/Site/Cloud/Azure (or Settings > Cloud > Azure) for either ABS or ADL2.

• Make sure that you have permissions to create, write, read, and delete blobs.

Existing content - mark as legacy

The following table summarizes the database providers that can connect to Azure database servers and describes their variants.

Many providers offer both "allow reconnect" and "no reconnect" variants. Select the "allow reconnect" option to enable database reconnect for long-running projects that may be susceptible to database disconnects due to cloud-based environmental factors or other issues.

"Allow reconnect" strategies will cause the RDBMS Input tool to spool records until the data set is complete and then move the entire data set at once, rather than streaming records as soon as they are available.

• ODBC and OLE DB providers have been deprecated in Data Management and may not be supported in future versions.

• Azure Data Warehouse OLE DB connections require the MSOLEDBSQL driver introduced by Microsoft in April 2018. This driver replaces the SQLNCI, SQLOLEDB, and SNAC drivers, which will not work with our OLE DB data provider.

• Azure Data Warehouse JDBC connections require the Microsoft JDBC Driver 8.2 for SQL Server introduced by Microsoft in March 2020.