Automating Azure SQL Database Scaling to Save Costs

Managing cloud costs efficiently is crucial, especially when dealing with Azure SQL Databases that may not need their full performance capacity 24/7. By automating the process of scaling down at night and scaling up in the morning, you can optimize costs while maintaining performance when needed.

In this post, I’ll show you how to use Azure Automation and PowerShell to automatically scale your Azure SQL Database.

Step 1: Create an Azure Automation Account

  • Go to the Azure Portal.
  • Navigate to Automation Accounts and click Create.
  • Provide a name, select your subscription, resource group (bradyupton_rg), and region.

  • Ensure that System Assigned Managed Identity is enabled (this will allow the automation account to interact with Azure resources).

Step 2: Assign Permissions to the Automation Account

  • Go to Azure SQL Server (bradyupton).
  • Navigate to Access Control (IAM) → Add Role Assignment.
  • Assign the Contributor or SQL Server Contributor role to your Automation Account’s Managed Identity.

Step 3: Create a Runbook for Scaling Down

  • In Azure Automation, go to Runbooks and click Create a Runbook.

  • Choose PowerShell as the runbook type

  • Add the following PowerShell script:
param 
(
[string]$resourceGroupName = "bradyupton_rg", #Change to your Resource Group
[string]$serverName = "bradyupton", #Change to your SQL Server
[string]$databaseName = "bradydatabase", #Change to your database
[string]$newSku = "Basic"  #Change to the desired lower tier
)

#Connect to Azure
Connect-AzAccount -Identity

#Scale down the SQL Database
Set-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName -Edition "Basic" -ComputeModel "Provisioned"

Write-Output "Database scaled down to $newSku"

  • Save and Publish the runbook.

Step 4: Schedule the Runbook

  • In the runbook, go to Schedules → Add a Schedule.

  • Set it to run every night at your desired time to scale down.

  • (Optional) Create a second runbook to scale up in the morning with a similar script but with a higher-tier SKU (e.g., S3).

Example for scaling up:

param 
(
[string]$resourceGroupName = "bradyupton_rg",
[string]$serverName = "bradyupton",
[string]$databaseName = "bradydatabase",
[string]$newSku = "S3"  # Higher-tier SKU for peak hours
)

#Connect to Azure
Connect-AzAccount -Identity

#Scale up the SQL Database
Set-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName -Edition "Standard" -RequestedServiceObjectiveName $newSku

Write-Output "Database scaled up to $newSku"

Conclusion

By automating SQL Database scaling with Azure Automation, you can significantly reduce costs during off-peak hours while maintaining performance when needed. This approach ensures your resources are utilized efficiently without manual intervention.

If you have any questions or need further customization, feel free to reach out!

Creating a Backup plan on SQL Express using Ola Hallengren’s scripts

SQL Express doesn’t have the SQL Server Agent so we can’t schedule jobs like normal. Follow this post to create a backup plan that will back up all the databases using Windows Task Scheduler.

  • Download CommandExecute – https://ola.hallengren.com/scripts/CommandExecute.sql
  • Download DatabaseBackup – https://ola.hallengren.com/scripts/DatabaseBackup.sql
  • Execute both of these stored procedures against the target server

Backups with Ola 1

  • Open Notepad on the target server and copy the following code changing the path to where the backups will be stored and the cleanup time. The cleanup time is specified in hours :
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backups',
@BackupType = 'FULL',
@Compress = 'Y', 
@CleanupTime = 48
  • Save the file with a .sql extension

Backups with Ola 2

  • Start the Task Scheduler by clicking on StartMenu/All Programs/Accessories/System Tools/Task Schedule
  • Click Create Basic Task to start the Scheduled Task Wizard
  • Type a name for the Task

Backups with Ola 3

  • Choose Daily from the scheduling options

Backups with Ola 4

  • Click Next, specify the information about the time to run the task. Set Start Time to an appropriate value when the load on the server is low. Set the recur every option to 1 day and click Next

Backups with Ola 5

  • Click Browse. Browse to SQLCMD.exe (C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE) and then click Open
  • Type the following content to the Add arguments text box and then click Next
    • –S ServerName –i “c:\SQLScripts\SQLBackups.sql”

Backups with Ola 7

  • Select the checkbox to Open the Advanced Properties for this task and click Finish

Backups with Ola 8

  • Check Run whether user is logged in or not and Run with highest privileges under Security Options then click OK.

Backups with Ola 9

**Note**

You may have to run this in the command prompt until it succeeds and copy exactly from CMD into the arguments section