How to scale Azure SQL DW up/down using a Logic app

1
votes

I know that logic apps can be created to resume/pause Azure SQL data warehouse and the same can be used within Azure Data Factory in a pipeline. I wanted to know if there is any way we can similarly create a logic app and use it in ADF to scale DW units up or down. Reason being, some components of my pipeline require the DW to be run at higher units whereas it can be kept at minimum for certain sections of the pipeline. Any help will be appreciated !

2
azureazure-sql-databaseazure-data-factoryazure-logic-apps

2 Answers

1
votes

Azure SQL Data Warehouse can be scaled up/down via PowerShell, T-SQL, or Rest API. A Logic App can execute a SQL statement, call Azure Functions that scale the DW up/down, or call the API directly.

Option 1: T-SQL. Add a t-SQL Action. There is a separate connector for Azure SQL DW (as opposed to DB). Make sure you connect to the Master database rather than the user database (this may be a deal breaker depending on your security requirements). Have it execute a query that is something like 

ALTER DATABASE mySampleDataWarehouse
MODIFY (SERVICE_OBJECTIVE = 'DW300c');

You may have to add a wait or a loop for polling because it takes several seconds for the scale to happen and you need to wait for that to finish.

Option 2: Azure Function. Create an Azure Function and use the Logic App to call that. There is a template available for the Function App to scale the DW up/down that you could potentially copy from. It uses a timer trigger, but you want an HTTP trigger to call from the Logic App. For this to work, you'll need a a Service Principal Account with contributor access under the same subscription as your data warehouse instance.

Edit: I'm not sure what you Data Factory control flow is like, but you could potentially just scale up/down in there rather than using a Logic App.

1
votes

If you do need to achieve this from a Logic App (eg you prefer the easy scheduling from Logic Apps rather than the CRON form in Azure Functions) or Logic Apps are your chosen integration solution, then it is possible. Simply use a HTTP task with PATCH call and the Azure SQL Data Warehouse REST API which has pause and resume methods and also allows you to pass in a body with the actual service level objective, eg

Logic App HTTP activity

In my example I used the following settings with Service Principal authentication:

Method:         PATCH
URI:            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
Headers:        (leave empty)
Queries:        (leave empty)
Body:           
{
"properties": {
    "requestedServiceObjectiveName": "DW100c"
    }
}
Authentication: Active Directory OAuth
Tenant:         (your tenant)
Audience:       https://management.core.windows.net/
Client ID:      (your client id)
Credential Type: Secret
Secret:         (your secret)

Set the service objective to whatever you want - my example uses DW100c which is actually part of a scale down operation: 100 is the lowest you can go. Note the example JSON in the REST API activity is incorrect / not valid JSON. Simply add quotes for a Gen 2 warehouse you will need to specify "c" after the service level objective, eg "DW3000c"

Diagram