Use Azure SQL Databases as Storage for your Instances

Azure SQL Single Database is a fully managed, database-as-a-service in the Microsoft Azure Cloud. This service is always running the latest SQL Engine, never require patches or updating and can be deployed in minutes. When configured using the "Serverless" Compute Tier, the database can even scale and pause automatically based on workload. It is an ideal solution for most cloud-based data warehouses utilizing fewer than billion row tables and requiring less than several terabytes of storage. 

In this article you will learn how to:

1. Configure an Azure SQL Database

Create an Azure SQL Database as storage for your Data Warehouse instance. Using the "Serverless" Compute Tier, the database can even scale and pause automatically based on workload.

Note: There are two common issues when using "Serverless" tier with TimeXtender. Initial Execution may fail when serverless is paused - to fix this issue, enable retires in your execution package with a 2-3 minute delay.

Furthermore, repeat executions may fail when serverless is scaling.  This is because the processing speed at lower vCores is still too slow. To fix it, ensure the Min vCores is raised to a reasonable limit, or very close to the Max vCores.

2. Configure SQL users & Permissions

You will need to create users in Azure SQL Database with the necessary permissions. Alternatively, you can use Azure Active Directory.

3. Setup an Instance with Azure SQL Database as storage

Create an ODX instance or a Data Warehouse instance using the relevant guide below, for the server name enter the 

For the server, copy the server name provided in Azure. 

4. Allow read access to the Database

The first step in the process is to give admin privileges to the user making the initial changes.

Learn More in Microsoft Docs

1. Identify the SQL Server in your resource group where the database is.
2. Inside the SQL Server resource click on the Azure Active Directory in the settings tab 

   

3. To set your user as the admin click on the Set admin option > Search and select the name in the dialogue box. 

   

4. In your SSMS Login to the Azure SQL as the Azure Active Directory admin specified above
5. Run the below script in SSMS connected to the same Azure SQL database

   CREATE USER [Azure AD Group Name] FROM EXTERNAL PROVIDER;

6. In TimeXtender desktop application where the Azure SQL DB, expand Security right click on the Database Role to Add: Add Database Role-> Add Manually-> Please enter the same AD Group Name used in the SSMS
7. To Provide the users in the Group access to only the valid schemas, right click Database Roles > Object Security Setup > Click on Schemas and check user Access Role to the valid schema.
8. Deploy the security tab

Optionally, to give the users read access to all the tables in the database, run the below script in SSMS

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