Table of contents

• Introduction
• Deploying Process
• Avoid SQL Server Timeout Issues Due to Deadlock

Introduction

At this point, you can deploy Azure resources (i.e. Apps, Database etc.) using bicep parameter file that you’ve created in the previous step.

Deploying Process

You can deploy resources using Azure CLI. Go to Azure.IaC folder (Please contact us for it). Open powershell.

To login, run following command, choose your Azure Entra ID (Active directory) user.

az login 

Then, run following command (please use your resource group name and bicep parameter file name)

az deployment group create --name IlapAnalyticsDeployment --resource-group 'your-resource-group-name' --template-file main.bicep --parameters your_param_file_name.bicepparam

Grant access to applications to use SQL Databases. For each database, sign in using Microsoft Entra authentication to the database and run the script below after replacing the user name. You need to do that for both analytics API and background job app.

create user [name of your deployed app] from external provider

--Grant roles required to read/write and perform migrations from Entity Framework
alter role [db_datareader] add member [name of your deployed app]
go
alter role [db_datawriter] add member  [name of your deployed app]
go
alter role [db_ddladmin] add member  [name of your deployed app]
go

--Grant execute permissions to application
--Create the role
CREATE ROLE db_executor;
GO

--Grant EXECUTE permission on the entire database to the role
GRANT EXECUTE TO db_executor;
GO

-- Add a user to the role (replace with app service name)
alter role [db_executor] add member  [name of your deployed app]
GO

Avoid SQL Server Timeout Issues Due to Deadlock

When performing large numbers of database operations, you may encounter timeout issues related to SQL Server deadlocks (known issue).
To mitigate this issue, adjust MAXDOP (official guide) for your SQL Server or database to achieve optimal parallelism while avoiding deadlocks. The recommended setting is to set MAXDOP to current value minus 1.

See current MAXDOP value

    -- See current value of maxdop

    USE [YOUR_DATABASE_NAME]; 
    SELECT name, value 
    FROM sys.database_scoped_configurations 
    WHERE name = 'MAXDOP'

    -- Set MAXDOP for specific database; 

    USE [YOUR_DATABASE_NAME]; 
    ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = Current_Value_Minus_1; -- Example: If you have 4, set MAXDOP to 3