Scaling Azure Dedicated SQL Pools from ADF

Case
Is there a solution to upscale and downscale my Azure Dedicated SQL Pool from the Azure Data Factory pipeline without scripting? I know there are PowerShell solutions, but I rather use a no-code solution. What are my options?

Scaling Azure Dedicated SQL Pools

 

Solution
Fortunately you can now use the Rest API's of Azure Dedicated SQL Pools (formerly known as Azure SQL Data Warehouse and for a short period as Azure Synapse Analytics) to down- or upscale the compute. So no coding required.

1) Give ADF Access to SQL Pool
To call the Rest API we need to give ADF access to the SQL Pool or more specific to the SQL Server hosting that SQL Pool. We need a role that can only change the database settings, but nothing security related: Contributer, SQL DB Contributer or SQL Server Contributer.

• Go to the Azure SQL Server of the SQL Pool that you want to scale up or down with ADF
• In the left menu click on Access control (IAM)
• Click on Add, Add role assignment
• In the 'Role' drop down select 'SQL DB Contributer'
• In the 'Assign access to' drop down select Data Factory
• Search for your Data Factory, select it and click on Save

Grant data factory SQL DB Contributor role to SQL Server

If you forget this step then you will receive an authorization error while executing your ADF pipeline.

2108 Authorization Failed

 

{"error":
{"code":"AuthorizationFailed"
,"message":"The client 'xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' with object id 'xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' does not have authorization to perform action 'Microsoft.Sql/servers/databases/resume/action' over scope '/subscriptions/xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/RG_bitools/providers/Microsoft.Sql/servers/SQL_bitools/databases/bitools' or the scope is invalid. If access was recently granted, please refresh your credentials."}
}

2) Determine URL

Now it is almost time to edit your ADF pipeline. The first step will be adding a Web activity to call the Rest API, but before we can do that we need to determine the URL of this API which you can find here.

Scaling

https://management.azure.com/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/Microsoft.Sql/servers/{server-name}/databases/{database-name}?api-version=2014-04-01-preview

Within this URL you need to replace all parts that start and end with a curly bracket: {subscription-id}, {resource-group-name}, {server-name} and {database-name} (including the brackets themselves). Don't use a URL (bitools.database.windows.net) for the database server name, but use only the name: bitools.

Example URL

https://management.azure.com/subscriptions/aaaa-bbbb-1234-cccc/resourceGroups/RG_Bitools/providers/Microsoft.Sql/servers/bitools2/databases/bitools?api-version=2014-04-01-preview

Example URL

3) JSON message for Rest API

The Rest API above expects a JSON message with the pricing tier. A list of all pricing tiers can be found here in the column 'Data warehouse units'. Here are two example which you need to adjust for your requirements:

{
    "properties": {
        "requestedServiceObjectiveName": "DW200c"
    }
}

or

{
    "properties": {
        "requestedServiceObjectiveName": "DW1000c"
    }
}

Note: Just in case you get one of these errors below. The json example in the documentation is incorrect at the moment of writing:

• Quotation marks around the data warehouse units are missing, which returns the following error:{"error":{"code":"InvalidRequestContent","message":"The request content was invalid and could not be deserialized: 'Unexpected character encountered while parsing value: D. Path 'properties.requestedServiceObjectiveName', line 3, position 41.'."}}
• The c is missing after the data warehouse units (gen1 vs gen2) which returns the following error:
  {"code":"45122","message":"\u0027Azure SQL Data Warehouse Gen1 has been deprecated in this region. Please use SQL Analytics in Azure Synapse.\u0027","target":null,"details":[{"code":"45122","message":"\u0027Azure SQL Data Warehouse Gen1 has been deprecated in this region. Please use SQL Analytics in Azure Synapse.\u0027","target":null,"severity":"16"}],"innererror":[]}

4) Add Web Activity
To call the Rest API we will use the Web Activity in the ADF pipeline. The actual Rest API call is synchronous, which means it waits for it to finish the pause or resume action and then it will return a message. This also means that you don't have to build any checks to make sure it is already online.

• Add a Web activity to your pipeline and give it a suitable name
• Go to the Settings tab and use the URL from step 2 in the URL property
• Choose PATCH as method
• Add a new header with the name 'Content-Type' and the value 'application/json'
• Fill in the JSON message from step 3 as body
• Choose MSI as authentication method
• As the last step enter this URL https://management.azure.com/ as Resource

Use a Web activity to call the Rest API

If you want to scale up before your ETL/ELT process and scale down afterwards then you need two separate Web activities or one clever child pipeline with parameters that you execute from your main pipeline.

Summary

In this post you learned how to upscale and downscale your Dedicated SQL Pool to save some money on your Azure bill without writing any code. Note that at the moment of writing live scaling is not yet available and that you will loose the connection to your Dedicated SQL Pool for a couple of minutes.

Also note that ADF pipelines slightly differ from Azure Synapse Analytics pipelines. So if you consider switching to Synapse workspaces because you apparently already use Dedicated SQL Pools then you have to make some small adjustments to this specific task which will be described in a next post. In an other post we already showed how to pause and resume your Azure SQL Pools from within ADF.