SQL Data Warehouse Automatic Scaling Template
Table of Contents
This package of Azure functions can be implemented either through the template listed above or via deployment. In both cases, you will need the following information:
- Name of the resource group your SQL DW instance is in
- Name of the logical server your SQL DW instance is in
- Name of your SQL DW instance
- Tenant ID (Directory ID) of your Azure Active Directory
- Subscription ID
- Service Principal Application ID
- Service Principal Secret Key
Information on how to get some of this information is listed in the manual steps.
In order to deploy through a template, click on the following deploy template button and follow the steps provided. If the link does not work, copy the URL and paste it manually in your browser.
If you choose to deploy the solution manually, download this repository and open up the corresponding Visual Studio solution.
Create an Azure Function App Service in the Azure portal. During this stage, we advise choosing a Consumption plan for pricing. This has a limitation of 5 minutes for runtime, but the Autoscaler will complete under this time frame. The template uses the Consumption plan by default. You should create a storage account with App Service.
Create an Azure Active Directory App (Service Principal). You'll want to ensure you retrieve your Service Principal Application (SPA) Id and Secret Key during this step. Assign the SPA to your resource groups where your SQL Data Warehouse is located with at least contributor privilege.
Get the Directory ID of your Azure Active Directory Tenant located in the porta.azure.com > Azure Active Directory > Properties.
Edit the dwuconfigs.json file. This file determines the level and order of scaling up/down. You can remove/add dwu config string in the file to skip/add some scaling levels. “DefaultDwu” is used by ScaleSqlDwByTimer function to scale SQL DW to a default level at certain time if current level is lower than default level. This file is unchanged by default in the template.
Deploy to your Azure function app. Follow the instructions here to publish the code package to your Azure account. You may have to install certain NuGet packages to publish Azure functions.
Add appropriate application settings for the function app in the portal. Azure functions does not use the local.settings.json or appsettings.json file. You must manually set up function application settings through the portal.
- Navigate to the Azure function app in the portal > Platform features > General Settings > Application Settings
- Fill in correct details
- IsEncrypted": false
- AzureWebJobsDashboard": ""
- AzureWebJobsStorage": "DefaultEndpointsProtocol=https;AccountName=;AccountKey=;"
- SqlDwLocation: "
- DwuConfigFile: "D:\home\site\wwwroot\dwuconfigs.json"
- DwScaleLogsTable: "DwScaleLogs"
- ScaleUpScheduleStartTime: ""
- ScaleUpScheduleEndTime: ""
- SubscriptionId: ""
- TenantId: ""
- ClientId: ""
- ClientKey: ""
- WEBSITE_TIME_ZONE: ""
Get function URL. Click on the ScaleSqlDw function from the list of functions in the left side Functions panel. Click on Get function URL from the top right panel area and copy the URL.
Create two Azure alerts in the SQL DW DWU usage monitoring page. Create one alert for scale down action, the other for scale up action. Make sure alert names contains key words "Scale Down" or "Scale Up". The function relies on the two keywords to perform scale down or scale up. When you edit rule, paste the function URL you copied from previous step to "Webhook" text box.
Please feel free to support this sample or others by opening a pull request.