Use Azure SQL Databases as Storage for Instances

Note: This article is intended for Azure database administrators. Ensure the following tasks are completed by someone with the appropriate Azure database administration privileges.

Azure SQL Database overview

Azure SQL Single Database is a fully managed, database-as-a-service in the Microsoft Azure Cloud. It runs the latest SQL Engine, requires no patches or updates, and can be deployed in minutes.

For development workloads, configure using the General Purpose Service Tier with Serverless Compute Tier for automatic scaling and cost optimisation.

For production workloads requiring heavy log and I/O throughput, the Hyperscale Service Tier with Premium-series hardware delivers maximum scalability and best-in-class transaction log performance, ideal for data warehousing operations.

Note: Multiple data areas (such as a staging area and a data warehouse) can be configured in a single Prepare instance using the same Azure SQL database.

Step 1: Configure an Azure SQL database

Development environments:Create an Azure SQL Database as storage for your Prepare instance. Using the General Purpose Service Tier with Serverless compute, the database automatically scales and pauses based on workload, providing cost-effective resource management with minimal storage costs when inactive.

Production environments: Use the Hyperscale Service Tier with Provisioned compute and Premium-series hardware. Hyperscale with Premium-series hardware provides the best log throughput (150 MiB/s) of any Azure SQL DB tier, delivering optimal performance for Jet Analytics ETL workloads that require intensive data warehouse operations.

Note:

Deployment considerations for Jet Analytics Data Integration:

Development workloads: Initial execution may fail when the serverless database is paused. Configure retries with 2–3 minute delays in your execution package to handle database resume operations.

Production workloads: Repeat executions may fail when the serverless database is scaling due to reduced processing capacity at lower vCore counts. Configure Min vCores to match Max vCores to prevent scaling-related connection drops, or use the Orchestration Cloud Optimizer to trigger scale operations before scheduled executions.

Step 2: Configure SQL users and permissions

Create users in Azure SQL Database with the necessary permissions. Azure Active Directory authentication may be used as an alternative.

Step 3: Set up an instance with Azure SQL Database as storage

Create an Ingest instance or a Prepare instance in the Jet Analytics Portal. For the Server name, enter the server name provided in Azure.

Step 4: Allow read access to the database

The following steps configure Azure Active Directory authentication and grant read access to the database. For more information, see Configure Azure AD authentication.

In the Azure portal, identify the SQL Server resource in your resource group that hosts the database.
Open the SQL Server resource and click Azure Active Directory under the Settings section.
Click Set admin, search for and select the user account you want to set as admin, and save.
In SQL Server Management Studio (SSMS), connect to the Azure SQL database using the Azure Active Directory admin account set in step 3.
Run the following script in SSMS while connected to the Azure SQL database to create a user from your Azure AD group:
CREATE USER [Azure AD Group Name] FROM EXTERNAL PROVIDER;
In Jet Analytics Data Integration, open the Prepare instance, expand Security, right-click Database Roles, and select Add Database Role > Add Manually. Enter the same AD group name used in step 5.
To restrict access to specific schemas only, right-click Database Roles > Object Security Setup, click Schemas, and select the user access role for the relevant schema.
Deploy the Security tab.

To grant users read access to all tables in the database, optionally run the following script in SSMS:

EXEC sp_addrolemember 'db_datareader', 'AD Azure Group';