Azure is a great cloud platform to handle all your data initiatives. You can easily build pipelines with Azure Data Factory to copy data from Blob storage into Azure SQL Tables. With mapping dataflows, you can transform data using a user interface. Or you could lift and shift your SSIS (SQL Server Integration Services) Packages to the cloud and run them in Azure Data Factory using an Azure-SSIS Integration Runtime. These are just a few options to extract data, and integrate it into a data platform.
At some point you want to automate your data warehouse tasks like scaling your DTU-based Azure SQL Databases, processing your Azure Analysis Services model, pausing your Azure-SSIS Integration Runtime,… You can use Azure Data Factory to trigger automation tasks using the web component or the webhook component.
Two Methods for automated scaling of an Azure SQL Database
The first method will perform the scaling task by using the Azure REST API. The second method performs the same task but uses Azure PowerShell modules.
1. Scaling Azure SQL Database with the Azure REST API
In the first example Azure Data Factory will run a pipeline, at the very beginning of the pipeline a web component will make a PATCH call to update the SKU of the Azure SQL Database.
Scaling method with REST API
In this example, we have built a simple pipeline which scales up the Azure SQL Database at the beginning of the pipeline and once the SSIS packages ran the database will be scaled down to reduce the cost.
ADF Demo pipeline with web activity
To perform the scaling action, you should use the underneath API request. Adjust the URI parameter which has been marked in bold and copy it to your clipboard.
https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}
/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}?api-
version=2019-06-01-preview
Take the edited URL and paste it in the URL
Configuring the web activity
In the body add the properties you want to update. To scale a standard tier database, you need to add name and tier. You can find all the adjustable properties on this Microsoft documentation page. In this example we are not using parameter for the sake of simplicity.
For authentication use MSI (Azure Managed Service Identity) and specify
Azure as the resource.
The configuration part in Azure Data Factory is finished, now with Access Control (IAM) we need to add permissions towards the Managed Identity so that it can perform the scaling operation. Always remember that these roles should be as restrictive as possible! For this example, we have assigned the Data factory Service Principal the Contributor role on the Azure SQL Server resource.
Adding permissions to the Managed Identity
Here are some other handy Azure REST API calls. They can be implemented in a similar way using a web component in ADF and assigning the ADF Service principal the required roles.
• Azure Synapse | Pausing an instance:
POST
https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-
group-name}/providers/Microsoft.Sql/servers/{server-name}/databases/{database-
name}/pause?api-version=2014-04-01-preview HTTP/1.1
• Azure Analysis Services | Pausing an instance:
POST
https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroup
Name}/providers/Microsoft.AnalysisServices/servers/{serverName}/suspend?api-version=2017-08-01
• There are many more. Check this Microsoft page for the complete Azure collection
Schematic view with PowerShell
2. Scaling Azure SQL Database with the Azure PowerShell modules
In this example we will use Azure Data Factory as an orchestrator similar to the previous example. But now the pipeline will start with a web component that called a webhook . A webhook makes it possible for external services to initiate Azure Automation Runbook by sending a HTTP request. An Azure Automation Runbook contains code that will be executed once the Runbook has been initiated. There are 4 types of runbooks: Graphical runbook, PowerShell runbook, PowerShell Workflow runbook, Python Runbook. For this example, we will use a PowerShell runbook.
The first step is to configure the Run as account for the Azure Automation Account. This is required to perform the scaling process initiated by the PowerShell script.
Setting up the Azure Run as Account
Create a new PowerShell notebook, copy the script below and save it.
Creating an Azure Automation Runbook
The following PowerShell script takes some parameter as an input to perform the scaling.
param(
[string] $resourceGroupName = "**************",
[string] $SubscriptionId = "********-****-****-****-************",
[string] $Location = "North Europe",
[string] $ServerName = "***********",
[string] $DatabaseName = "****************",
#[string] $azureProfilePath = "c:\~temp\AzureRMContext.txt",
[string] $azureProfilePath = "",
[string] $azureRunAsConnectionName = "AzureRunAsConnection",
[Parameter (Mandatory = $false)]
[object] $WebhookData
)
$VerbosePreference = "Continue"
$ErrorActionPreference = "Stop"
#If runbook was called from webhook, webhookdata will be NULL.
if ($WebhookData) {
# Collect properties of WebhookData
$WebhookBody = $WebhookData.RequestBody
# Collect individual headers. Input converted from JSON.
$Input = (ConvertFrom-Json -InputObject $WebhookBody)
Write-Verbose "WebhookBody: $Input"
}
else
{
Write-Error -Message 'Runbook was not started from Webhook' -ErrorAction stop
}
$DTU = $Input.Size
$Source = $Input.Origin
write-output $DTU
write-output $Source
Write-Verbose "Logging in to Azure..."
if($Source -ne "ADF-Webhook")
{
write-output "Runbook was not started from Webhook"
Write-Error -Message 'Runbook was not started from Webhook' -ErrorAction stop
}
# Load the profile from local file
if (-not [string]::IsNullOrEmpty($azureProfilePath))
{
Import-AzureRmContext -Path $azureProfilePath | Out-Null
}
# Load the profile from Azure Automation RunAS connection
elseif (-not [string]::IsNullOrEmpty($azureRunAsConnectionName))
{
$runAsConnectionProfile = Get-AutomationConnection -Name $azureRunAsConnectionName
Add-AzureRmAccount -ServicePrincipal -TenantId $runAsConnectionProfile.TenantId `
-ApplicationId $runAsConnectionProfile.ApplicationId -CertificateThumbprint $runAsConnectionProfile.CertificateThumbprint | Out-Null
}
# Interactive Login
else
{
Add-AzureRmAccount | Out-Null
}
Write-Host " "
Write-Host " Scaling Database "
Set-AzureRmSqlDatabase -DatabaseName $DatabaseName `
-ServerName $ServerName `
-ResourceGroupName $ResourceGroupName `
-Edition "Standard" `
-RequestedServiceObjectiveName $DTU
Now create a webhook which we can call with a web activity in Azure Data Factory. Fill in a name for the webhook and set an expiry date. Be aware that after the webhook expires you will need to create another webhook!
Store the provided URL in a secure place since you have 1 shot to copy the generated URL.
Creating a webhook for a Runbook
The next step is to provide parameters to the webhook.
Configuring Parameters for the webhook
Save the webook and go to your Azure Data Factory pipeline to create a new webactivity. If the web activity runs it will make a POST call over http to the webhook. This webhook will trigger the PowerShell notebook to perform the scaling.
Configuring web activity for the webhook
Conclusion
We highlighted 2 methods to show how you can scale your Azure SQL Database by using Azure Data Factory as the trigger. We would recommend the first method which uses the Azure REST API: this requires less work to set up, has less failure risk and is easier to maintain. On top of that, the API will perform the scaling quicker since it is also possible for the Automation Runbook to wait in queue to be executed.
Keen to know more?
Continue reading or contact us to get started:
- A best-practice Modern Data Platform with Azure Databricks and Delta
- When to use Azure Synapse Analytics and/or Azure Databricks?
- Azure Data Factory & Airflow: mutually exclusive or complementary?
