Auto scale Azure SQL Databases with Azure Logic Apps

Introduction

In this article we will demonstrate how to schedule scaling of SQL Databases with Azure Logic Apps.

As illustrated in the following diagram:

1. Azure Logic Apps will be mapped to a User Managed Identity that has the Azure built-in role “SQL DB Contributor”, this role will permit the Logic Apps to get and update the SQL Databases SKUs.
2. Azure Logic Apps will run automated recurring workflows on a schedule. An another scenario could be to trigger them through a native Azure metrics.
3. Each Azure Logic Apps will have as parameters a list of databases with their desired SKUs.

Workflow

As illustrated in the following screenshot, the Azure Logic App workflow consists in:

1. Geting all Databases current properties through the “Databases — Get” API.
2. Comparing if the current Database’s SKU is set as desired.
3. Updating the Database’s SKU if it is not set like the Logic Apps parameters through the “Databases — Create Or Update” API.

Deployment

All the code is available in the following git repository : Create-AzSqlDatabaseAutoScaling.

The following PowerShell code demonstrates how to create an Azure Logic App that will scale down two databases at 8 pm every days except on weekends.

# Variables
$RgName = "<An existing Resource Group Name for the Logic Apps>"
$UserAssignedIdentityRgName = "<An existing User Identity Resource Group Name>"
$UserAssignedIdentityName = "<An existing User Identity Name>"
$SqlServerId = "/subscriptions/xxxxx-xxxxx-xxxxx-xxxxx-xxxxx/resourceGroups/<Resource Group name>/providers/Microsoft.Sql/servers/<Azure SQL Server name>"
$TemplateUri = "https://raw.githubusercontent.com/JamesDLD/AzureRm-Template/master/Create-AzSqlDatabaseAutoScaling/template.json"
$UserAssignedIdentity = Get-AzUserAssignedIdentity -ResourceGroupName $UserAssignedIdentityRgName -Name $UserAssignedIdentityName

# Action
## Assign the role to the managed identity, for more information on this procedure you chec have a look on the following [reference](https://learn.microsoft.com/en-us/azure/role-based-access-control/role-assignments-powershell?WT.mc_id=AZ-MVP-5003548#step-4-assign-role).
New-AzRoleAssignment -ObjectId $UserAssignedIdentity.PrincipalId -RoleDefinitionName "SQL DB Contributor" -Scope $SqlServerId

## Deploy an Azure Logic App
Write-Host "Deploying to the resource group : $RgName Azure Logic Apps that will scale SQL Databases" -ForegroundColor Cyan

$parameters = @{
    tags = @{
        role = 'Scale down Azure SQL Databases'
    }
    userAssignedIdentityId = $UserAssignedIdentity.Id
    logicAppName = 'demo-scale-down-sql-logic'
    sqlServerId = $SqlServerId
    desiredSkusPerDatabases = @{
        target = @(
        @{
            name = "DEMO1"
            sku = @{
                capacity = 5
                name = "Basic"
                tier = "Basic"
            }
        }
        @{
            name = "DEMO2"
            sku = @{
                capacity = 5
                name = "Basic"
                tier = "Basic"
            }
        }
        )
    }
    recurrence = @{
        frequency = "Week"
        interval = 1
        schedule = @{
            hours = @("20")
            minutes = @(0)
            weekDays = @("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
        }
        timeZone = "Romance Standard Time"
    }
}

New-AzResourceGroupDeployment -Name "SqlDatabasesScaleDown" -ResourceGroupName $RgName `
  -TemplateUri $TemplateUri `
  -TemplateParameterObject $parameters

Conclusion

One of the biggest benefits of the cloud is the ability to only pay for what you need. Even though no native autoscaling functionality is available, you can still use the Azure API and tools like Logic Apps to meet your needs.

See You in the Cloud

Jamesdld