How To Scale Azure SQL Database In Azure Data Factory Using A Web Activity

Azure SQL Database is a very flexible service that can be easily scaled to fit the needs of the moment. In most solutions, I like to orchestrate the up and down scaling right from within Azure Data Factory, and I’m always interested in more efficient approaches to do this.

Until today, to scale up an Azure SQL Database before a particular workload in Azure Data Factory, I had quite some options to choose from to achieve this:

  • Create a LogicApp, and trigger it with a Web Activity in Azure Data Factory.
  • Create an Azure Function, and invoke the function using the new Azure Function Activity in Azure Data Factory.
  • Create an Azure Automation workbook, add a webhook to it and call that with a Web Activity in Azure Data Factory.
  • Create a T-SQL script with an ALTER DATABASE statement and call that with a Stored Procedure Activity in Azure Data Factory.

New kid in town (well, at least for me)

But today, my dear colleague Simon Zeinstra pointed out that the Azure Data Factory Web Activity supports managed identity (MSI) authentication, thanks 👊🏻. And I successfully put together another really good option: using the Azure Management REST API with a Web Activity in Azure Data Factory. And it is so simple and easy to set up, this probably is my favorite option from now on 😎.
The Web Activity seems to call the REST API synchronously, hence it does not finish before the scale operation is completed.

ADF Web Activity
Less is more: we only need a Web Activity.

Configuration

The following configuration for the Web Activity is what worked in my set up.

  • URL: Use the following URL, where you replace the stuff between {brackets}. Be sure to specify the Azure SQL Server without the “.database.windows.net” part.
    https://management.azure.com/subscriptions/{your SubscriptionID}/resourceGroups/{your RG}/providers/Microsoft.Sql/servers/{your sqlservername}/databases/{your databasename}?api-version=2017-10-01-preview
  • Methode: PUT
  • Headers: Add a header with name Content-Type, and value application/json.
  • Body: Use the following json body, where you specify the sku name and tier that you want your database to be scaled to, and the region of the database:
    {"sku":{"name":"P1","tier":"Premium"}, "location": "West Europe"}
  • Authentication: MSI
  • Resourcehttps://management.azure.com/

Authorisation

If you have completed your configuration and debug the Web Activity, you may get this error message:

{
"errorCode": "2108",
"message": "{\"error\":{\"code\":\"AuthorizationFailed\",\"message\":\"The client '{GUID}' with object id '{GUID}' does not have authorization to perform action 'Microsoft.Sql/servers/databases/write' over scope '/subscriptions/{subid}/resourceGroups/{rgid}/providers/Microsoft.Sql/servers/{sqlservername}/databases/{dbname}'.\"}}",
"failureType": "UserError",
"target": "Scale up ASQL DB"
}

This means the managed identity (MSI) of our Azure Data Factory does not yet have the correct permissions on our Azure SQL Server. Follow these steps to do this:

  1. In the Azure Portal, go to the SQL Server.
  2. Open the ‘Access control (IAM)’ option.
  3. Click ‘Add role assignment’.
  4. Choose role ‘Contributor’.
  5. In the search box for ‘Select’, type the exact name of your Azure Data Factory resource, and select it.
  6. Click Save.

Now the managed identity (MSI) of your Azure Data Factory is able to change the options of your database!

Quickstart ADF pipeline example

For your convenience, I’ve shared an example Azure Data Factory pipeline on GitHub.

Conclusion

You now know how to effortlessly use a Web Activity in Azure Data Factory to scale your Azure SQL Database.

The ease-of-use of this solution is awesome, and largely due to how the authentication between Azure Data Factory and Azure SQL is taken care of via managed identity (MSI). Keeping any credentials out of my code, and I don’t even have to use Azure Key Vault to accomplish this.

The other options I mentioned like LogicApps, Functions and Runbooks sure provide more features and options. Time will tell if this simple option is going to be my new default, but it sure seems like it 👍!

More info:

  • https://docs.microsoft.com/en-us/azure/data-factory/control-flow-web-activity
  • https://docs.microsoft.com/en-us/azure/sql-database/sql-database-single-database-scale
  • https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/overview
  • https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/services-support-msi
  • https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu
Pass the sauce
More from Dave Ruijter

Dave’s Gallery of Public Reports

Publishing your Power BI report to the internet, free for everyone to...
Read More

4 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *