Azure SQL DTU DB Scaling with Azure Automation Account

Introduction

In today’s dynamic cloud environment, managing database performance and cost efficiency is crucial. Azure SQL Database offers a flexible and scalable solution with its Database Transaction Unit (DTU) model. However, manual scaling can be time-consuming and prone to errors. This is where Azure Automation Account and PowerShell scripts come into play, enabling automated and scheduled scaling of SQL DTUs to meet varying workload demands efficiently.

Step-by-Step Implementation Guide Using Bicep

Set Up Azure Automation Account
- Navigate to the Azure portal and create a new Automation Account. Ensure you create a Run As Account during this process to manage authentication seamlessly.
Update PowerShell Modules
- Once the Automation Account is created, update the PowerShell modules to ensure you have the latest cmdlets. This can be done by importing a script to update the modules.

Create the Scaling Runbook

In the Automation Account, create a new runbook and select PowerShell as the type. Use the following script to define the scaling logic:

1
2
3
4
5
6
7
8
9


param(
    [string]$ResourceGroupName,
    [string]$ServerName,
    [string]$DatabaseName,
    [string]$DTU
)

$Database = Get-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName
$Database | Set-AzSqlDatabase -Edition "Standard" -RequestedServiceObjectiveName $DTU

Schedule the Runbook
- Schedule the runbook to run at specific times using the Azure portal. This ensures that the DTU scaling happens automatically based on your defined schedule.

Deploy Using Bicep

Create a Bicep file to deploy the necessary resources. Here’s a sample Bicep template:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


param location string = 'australiaeast'
param automationAccountName string = 'myAutomationAccount'
param runbookName string = 'ScaleSQLDTU'
param resourceGroupName string = 'myResourceGroup'
param serverName string = 'mySqlServer'
param databaseName string = 'myDatabase'
param dtu string = 'S3'

resource automationAccount 'Microsoft.Automation/automationAccounts@2020-01-13-preview' = {
  name: automationAccountName
  location: location
  properties: {}
}

resource runbook 'Microsoft.Automation/automationAccounts/runbooks@2020-01-13-preview' = {
  name: '${automationAccountName}/${runbookName}'
  properties: {
    runbookType: 'PowerShell'
    logProgress: true
    logVerbose: true
    description: 'Runbook to scale SQL DTU'
    draft: {
      inEdit: true
    }
  }
}

output runbookId string = runbook.id

Deploy the Bicep Template

Use Azure CLI or Azure PowerShell to deploy the Bicep template:

1

az deployment group create --resource-group myResourceGroup --template-file main.bicep

Conclusion

Automating SQL DTU scaling using Azure Automation Account and PowerShell scripts not only enhances performance management but also optimizes costs by scaling resources based on actual demand. By leveraging Bicep for deployment, you can ensure a streamlined and efficient setup process.

Learn More

For more detailed information and advanced configurations, refer to the following Microsoft Learn resources: