Schedule Up/Downscale Azure SQL Database (Az)

Case
To save some money on my Azure bill, I want to downscale the vCore or the number of DTUs for my Azure SQL Databases when we don't need a high performance and then upscale them again when for example the ETL starts. How do you arrange that in Azure?

Change the Tier of your SQL Azure DB

Solution
A few years ago we showed you how to do this with some PowerShell code in an Azure Automation Runbook with the AzureRM modules. However these old modules will be be out of support by the end of 2020. So now it is time to change those scripts.

The script works for both Database Transaction Units (DTU) and Virtuals Cores (vCore). DTU uses a Service Tier (Basic, Standard and Premium) and then the actual Performance Level (B, S0 to S12, P1 to P15) which indicates the number of used DTUs. More DTUs means more performance and of course more costs.

The newer vCore model is now recommended by Microsoft. It is a bit more flexible and transparent: it actually shows you the processor, memory and IOPS. But the lowest vCore price is much more expensive than the lowest DTU price. So for development and testing purposes you probably still want to use DTU. The vCore model also uses a Service Tier (GeneralPurpose, BusinessCritical) and then number of vCores combined with the processor (e.x. GP_Gen4_1 or GP_Gen5_2).

Use the Microsoft documentation to compare both models, but a little comparison. S3 with 100 DTUs is about the same as General Purpose with 1 vCore. P1 with 125 DTUs is about the same as Premium with 1 vCore.

1) Create Automation Account
First we need to create an Automation Account. If you already have one with the Run As Account enabled then you can skip this step.

• Go to the Azure portal and create a new resource
• Search for automation
• Select Automation Account
• Choose a useful name for the Automation Account
• Select your Subscription, Resource Group and the Region
• For this example we will use the Azure Run As account. So make sure to enable it and then click on the Create button.

Create Azure Automation Account

2) Add Module Az.Sql
Before we start writing some code we need to add a PowerShell module called Az.Sql. This module contains methods we need in our code to upscale or downscale the Azure SQL database, but first we need to add Az.Accounts because Az.Sql depends on it.

If you forget this step you will get error messages while running your code that state that some of your commands are not recognized:

Get-AzSqlDatabase : The term 'Get-AzSqlDatabase' is not recognized as the name of a cmdlet, function, script 
file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct 
and try again.

• Go to the newly created Azure Automation Account
• Go to Modules in the left menu
• Click on the Browse Gallery button
• Search for Az.Accounts
• Click on Az.Accounts in the result and import this module
• Also search for Az.Sql (but wait until Az.Accounts is actually imported)
• Click on Az.Sql in the result and import this module

Adding a new module to your Automation Account

Note: if you are using an existing Automation Account then you probably already added Az.Accounts.

3) Create Runbook
Now we are ready to create a runbook in the Azure Automation Account and start writing some PowerShell code.

• Go back to the overview page of your newly created Azure Automation Account
• Click on Runbooks in the left menu
• Click on the + Create a runbook button to create a new Runbook
• Enter a descriptive name for the Runbook like UpDownScaleDb
• Select PowerShell as Runbook type
• Optionally add a description and click on the Create button

Create a Runbook

4) Edit Runbook code
Next edit the new Runbook if it wasn't already opened by the previous step. Copy the code below and paste it in the editor. Then study the code and its comments to understand the code that can upscale and downscale your Azure SQL DB. It exists of five parts:

1. Parameters
2. Log in to Azure
3. Get current pricing tier
4. Upscale or downscale
5. Logging

Parameters
To up or downscale the script needs five parameters. The first three parameters 'ResourceGroupName', ServerName'' and 'DatabaseName' are to indicate for which database the DTU or vCore needs to be changed. The fourth and fifth parameters 'Edition' and 'PricingTier' are used to up or downscale your DB. There are a couple of parameter validations which you could extend to make your script even more monkey proof.
Note: if you want to call this script via Azure Data Factory (ADF), then you need to change the parameter part. You can find all the details to do that in our blog posts about Runbook parameters and ADF and using the Webhook activity in ADF. If this is your first time creating a runbook then first try the standard script and then adjust it to your needs.

Log in to Azure
This is a standard piece of code that you will see in all of our examples. Please read our blog post about the Azure Run as Account for more detailed information.

Get current pricing tier
This piece of code tests whether it can find the database and gets its current pricing tier. It stores the current pricing tier and uses it later on for an extra check when upscaling or downscaling the DB.

Upscale or downscale
This is the actual code for changing the database pricing tier. There is an extra check to compare the current pricing tier with the new pricing tier. It now throws an error when they are equal. You could change that to write an warning instead of an error.
Note: you could also send emails to notify you of any errors

Logging
The last piece of code is for logging purposes. It shows you that it successfully changed pricing tier of the DB (or DWH) and how long it took to accomplish that.

# PowerShell code
########################################################
# Parameters
########################################################
[CmdletBinding()]
param(
    [Parameter(Mandatory=$True,Position=0)]
    [ValidateLength(1,100)]
    [string]$ResourceGroupName,

    [Parameter(Mandatory=$True,Position=1)]
    [ValidateLength(1,100)]
    [string]$ServerName,

    [Parameter(Mandatory=$True,Position=2)]
    [ValidateLength(1,100)]
    [string]$DatabaseName,

    [Parameter(Mandatory=$False,Position=3)]
    [ValidateLength(1,100)]
    [string]$Edition,
    
    [Parameter(Mandatory=$False,Position=4)]
    [ValidateLength(1,100)]
    [string]$PricingTier
)

# Keep track of time
$StartDate=(GET-DATE)



########################################################
# Log in to Azure with AZ (standard code)
########################################################
Write-Verbose -Message 'Connecting to Azure'
 
# Name of the Azure Run As connection
$ConnectionName = 'AzureRunAsConnection'
try
{
    # Get the connection properties
    $ServicePrincipalConnection = Get-AutomationConnection -Name $ConnectionName       
  
    'Log in to Azure...'
    $null = Connect-AzAccount `
        -ServicePrincipal `
        -TenantId $ServicePrincipalConnection.TenantId `
        -ApplicationId $ServicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $ServicePrincipalConnection.CertificateThumbprint 
}
catch 
{
    if (!$ServicePrincipalConnection)
    {
        # You forgot to turn on 'Create Azure Run As account' 
        $ErrorMessage = "Connection $ConnectionName not found."
        throw $ErrorMessage
    }
    else
    {
        # Something else went wrong
        Write-Error -Message $_.Exception.Message
        throw $_.Exception
    }
}
########################################################
 


########################################################
# Getting the database for testing and logging purposes
########################################################
$MyAzureSqlDatabase = Get-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName
if (!$MyAzureSqlDatabase)
{
    Write-Error "$($ServerName)\$($DatabaseName) not found in $($ResourceGroupName)"
    return
}
else
{
    Write-Output "Current pricing tier of $($ServerName)\$($DatabaseName): $($MyAzureSqlDatabase.Edition) - $($MyAzureSqlDatabase.CurrentServiceObjectiveName)"
}



########################################################
# Set Pricing Tier Database
########################################################
# Check for incompatible actions
if ($MyAzureSqlDatabase.Edition -eq $Edition -And $MyAzureSqlDatabase.CurrentServiceObjectiveName -eq $PricingTier)
{
    Write-Error "Cannot change pricing tier of $($ServerName)\$($DatabaseName) because the new pricing tier is equal to current pricing tier"
    return
}
else
{
    Write-Output "Changing pricing tier to $($Edition) - $($PricingTier)"
    $null = Set-AzSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $ResourceGroupName -Edition $Edition -RequestedServiceObjectiveName $PricingTier
}



########################################################
# Show when finished
########################################################
$Duration = NEW-TIMESPAN –Start $StartDate –End (GET-DATE)
Write-Output "Done in $([int]$Duration.TotalMinutes) minute(s) and $([int]$Duration.Seconds) second(s)"

Note: once your database grows to a certain size you can not downscale it to certain levels because there is a max database size for certain pricing tiers. For example if your DB is larger than 250 GB then you can note downscale it below S3. There are size checks that you could add to avoid errors.

5) Testing
Testing the functionality of your code can be done in the runbook editor. Click on the Test pane button above your script. After that you need to fill in the five parameters and hit the Start button to execute the script.

Testing a Powershell script

6) Scheduling Runbook
To schedule your runbook in Azure Automation you first need to publish it via the Runbook editor. After it has been published you can add a schedule to this runbook.

• Edit the script in the runbook editor
• Click on publish (the editor will close and you will be redirected to the overview page)
• In the overview page click on Link to schedule
• In the Schedule menu you can select an existing schedule or create a new one
• In the Parameter menu you can provide the value for the five parameters

Add schedule to a runbook

Note: If you have multiple Azure SQL Database that you all want to up or downscale on the same time then you have a slight problem because you cannot reuse a schedule for the same runbook multiple times with different parameters (please upvote or add a comment). Workarounds:

1. create multiple identical schedules (ugly but works)
2. do everything in one big script (less flexible but works)

Log of runbook executions

Details of single execution

Summary
In this blog post you learned how to schedule an up- or scale of your Azure SQL DB (or DWH) to save money in case you don't need a high performace 24 hours a day. Scheduling is done in Azure Automation, but with some minor changes you can also do that via an ADF pipeline.

The Powershell scripting gives you the most flexibility for example to add additional tests and notifications, but for those who don't like scripting: in a next blog post we will show you how to accomplish this with TSQL code only. You can either use that for an on-the-fly up- or downscale or use it in a Store Procedure Activity in Azure Data Factory.

Azure snack: prevent accidental deletes (with locks)

Case
It probably never happened to you, but how do you prevent accidental deletions of Azure resources by you or your co-workers?

Oops deleting the wrong resource :-(

Solution
Accidental deletions of Azure resources (for example by clicking on the wrong button) are probably very rare because you have to confirm the deletion by typing the name of the resource. Deleting the wrong resource (for example a resource with the same name in a different resource group) is more likely.

Microsoft added a very simple, but also very effective solution for this with the Locks option. You can add locks on Subscription level, Resource Group level and Resource level. There are two different type of locks:

• Read-only: Authorized users can't change a thing and therefor they also can't delete the resource
• Delete: Authorized users can change the resource, but they can't delete the resource

Note: although you might be a contributor or owner for the resource. A lock overrides that authorization.

1) Determine level and lock type
First step is to determine which resource(s) you want to protect and where you want to add the lock. On the Subscription level is probably a bit too protective in most cases and on individual resources might be too much work. Resource group is the most likely choice. Next step is to decide which lock you want to add: read-only or delete. If you want to exaggerate you could even add multiple locks on the same level or on different levels.

2) Add lock
Now it's time to add the actual lock for preventing an unwanted delete.

• Go to the Subscription, Resource Group or Resource and click in the left menu on Locks. On Subscription level it is called 'Resource locks'.
• Click on the + Add button
• Enter the Lock name. E.g. 'Prevent Accidental Deletes'
• Choose the lock type: read-only or delete
• Enter a note why you have added this lock and hit the OK button

Add a delete lock

Note: Removing a lock can be done in the same spot.

3) Testing

To test this you probably want to create a dummy resource and then try to delete it. When you delete the resource it initially looks like you will succeed, but then you will get the following error message (it also shows where you can find the lock).

Failed to delete logic app AWS.
The scope '/subscriptions/1x1111x1-1x1x-1111-xx11-1x1111x1x11x/resourceGroups/Joost_van_Rossum/providers/Microsoft.Logic/workflows/AWS' 
cannot perform delete operation because following scope(s) are locked: '/subscriptions/1x1111x1-1x1x-1111-xx11-1x1111x1x11x/resourceGroups/Joost_van_Rossum'.
Please remove the lock and try again.

Try to delete resource with lock

Summary
In this post you saw how to use locks for (accidental) delete prevention of Azure resources. Probably most useful for mixed environments to protect the production resources. There are a few considerations when using locks:

To add or remove a Lock you need to have the role Owner or User Access Administrator. This means you can also use a deletion Lock to really prevent deletions by people with the Contributer role. They can still edit the resource, but are not allowed to delete the resource.

If you have Azure Automation runbooks scheduled to upscale or downscale a resource with a read-only lock then you first have to remove the lock in the PowerShell script before making the changes. Same applies to Azure DevOps when your deployment changes a resource in Azure with a lock.

The locks only prevents 'Azure changes'. However it does not prevent deletions or changes within for example an Azure SQL database.

Schedule start and stop of Azure Virtual Machine (Az)

Case
There is an option to automatically shutdown your Azure Virtual Machine at a certain time, but where is the option to automatically start it at a certain time?

Auto-shutdown Auzre VM

Solution
In 2017 we showed you how to solve this with a scheduled PowerShell Runbook in Azure Automation. That post used the old Azure_RM PowerShell modules which will no longer be supported by the end of 2020. Therefor we have rewritten the code to use the Az PowerShell modules and made a few tweaks and extra checks. It can also shutdown the VM if you want to do everything in one place.

1) Create Automation Account
First we need to create an Automation Account to host our PowerShell code. If you already have one with the Run As Account enabled then you can skip this step.

• Go to the Azure portal and create a new resource
• Search for automation
• Select Automation Account
• Choose a useful name for the Automation Account
• Select your Subscription, Resource Group and the Region
• For this example we will use the Azure Run As account. So make sure to enable it and then click on the Create button.

Create Azure Automation Account

2) Add Module Az.Compute
Before we start writing code we need to add a PowerShell module called Az.Compute. This module contains Virtual Machine methods we need in our code. But first we need to add Az.Accounts because Az.Compute depends on it.

If you forget this step you will get error messages while running your code that state that some of your commands are not recognized:

Get-AzVM : The term 'Get-AzVM' is not recognized as the name of a cmdlet, function, script 
file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct 
and try again.

• Go to the newly created Azure Automation Account
• Go to Modules in the left menu
• Click on the Browse Gallery button
• Search for Az.Accounts
• Click on Az.Accounts in the result and import this module
• Also search for Az.Compute (but wait until Az.Accounts is actually imported)
• Click on Az.Compute in the result and import this module

Adding a new module to your Automation Account

Note: if you are using an existing Automation Account then you probably already added Az.Accounts.

3) Runbook
Now we are ready to create a runbook in the Azure Automation Account and start writing some PowerShell code.

• Go back to the overview page of your newly created Azure Automation Account
• Click on Runbooks in the left menu
• Click on the + Create a runbook button to create a new Runbook
• Enter a descriptive name for the Runbook like StartStopVM
• Select PowerShell as Runbook type
• Optionally add a description and click on the Create button

Create a Runbook

4) Edit Script
Next edit the new Runbook if it wasn't already opened by the previous step. Copy the code below and paste it in the editor. Then study the code and its comments to understand the code that can both start and stop your Azure Virtual Machine (VM). It exists of five parts:

1. Parameters
2. Log in to Azure
3. Get current state
4. Pause or Resume
5. Logging

Parameters
To pause or resume the script needs three parameters. The first parameter 'VirtualMachineAction' is a string that indicates whether you want to stop or start the VM. The second parameter 'ResourceGroupName' indicates the location (resourcegroup) of your VM and the last parameter 'VirtualMachineName' is the name of your VM. There are a couple of validations which you could extend to make your script even more monkey proof.

Log in to Azure
This is a standard piece of code that you will see in all of our examples. Please read our blog post about the Azure Run as Account for more detailed information.

Get current state
This piece of code tests whether it can find the VM and gets its current state. It stores the current state and uses it later on for an extra check when stopping or starting the VM.

Stop or Start
This is the actual code for stopping or starting the VM. There is an extra check to compare the current state with the new desired state. It now throws an error when you want to stop a VM that is already stopped. You could change that to write an warning instead of an error.

Error

Note: you could also send emails to notify you of any errors

Logging
The last piece of code is for logging purposes. It shows you that it successfully changed the state of the VM and how long it took to accomplish that.

# PowerShell code

########################################################
# Parameters
########################################################
[CmdletBinding()]
param(
    [Parameter(Mandatory=$True,Position=0)]
    [ValidateSet('Start','Stop')]
    [string]$VirtualMachineAction,
    
    [Parameter(Mandatory=$True,Position=1)]
    [ValidateLength(1,100)]
    [string]$ResourceGroupName,

    [Parameter(Mandatory=$True,Position=2)]
    [ValidateLength(1,100)]
    [string]$VirtualMachineName
)

# Keep track of time
$StartDate=(GET-DATE)



########################################################
# Log in to Azure with AZ (standard code)
########################################################
Write-Verbose -Message 'Connecting to Azure'
 
# Name of the Azure Run As connection
$ConnectionName = 'AzureRunAsConnection'
try
{
    # Get the connection properties
    $ServicePrincipalConnection = Get-AutomationConnection -Name $ConnectionName       
  
    'Log in to Azure...'
    $null = Connect-AzAccount `
        -ServicePrincipal `
        -TenantId $ServicePrincipalConnection.TenantId `
        -ApplicationId $ServicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $ServicePrincipalConnection.CertificateThumbprint 
}
catch 
{
    if (!$ServicePrincipalConnection)
    {
        # You forgot to turn on 'Create Azure Run As account' 
        $ErrorMessage = "Connection $ConnectionName not found."
        throw $ErrorMessage
    }
    else
    {
        # Something else went wrong
        Write-Error -Message $_.Exception.Message
        throw $_.Exception
    }
}
########################################################
 



########################################################
# Getting the VM for testing and logging purposes
########################################################
$myVirtualMachine = Get-AzVM -ResourceGroupName $ResourceGroupName -Name $VirtualMachineName
if (!$myVirtualMachine)
{
    Write-Error "$($VirtualMachineName) not found in $($ResourceGroupName)"
    return
}
else
{
    # Get status of VM when user provides data for existing VM
    $myVirtualMachineState = ((Get-AzVM -ResourceGroupName $ResourceGroupName -Name $VirtualMachineName -Status).Statuses | Where {$_.code -like 'PowerState/*'}).Code
    Write-Output "Current status of $($VirtualMachineName): $($myVirtualMachineState)"
}



########################################################
# Start or Stop VM
########################################################
# Check for incompatible actions
if (($VirtualMachineAction -eq "Start" -And $myVirtualMachineState -eq "PowerState/running") -Or ($VirtualMachineAction -eq "Stop" -And $myVirtualMachineState -eq "PowerState/deallocated"))
{
    Write-Error "Cannot $($VirtualMachineAction) $($VirtualMachineName) while the status is $($myVirtualMachineState)"
    return
}
# Resume Azure Analysis Services
elseif ($VirtualMachineAction -eq "Start")
{
    Write-Output "Now starting $($VirtualMachineName)"
    Start-AzVM -ResourceGroupName $ResourceGroupName -Name $VirtualMachineName -Confirm:$false
}
# Pause Azure Analysis Services
else
{
    Write-Output "Now stopping $($VirtualMachineName)"
    $null = Stop-AzVM -ResourceGroupName $ResourceGroupName -Name $VirtualMachineName -Confirm:$false -Force
}



########################################################
# Show when finished
########################################################
$Duration = NEW-TIMESPAN –Start $StartDate –End (GET-DATE)
Write-Output "Done in $([int]$Duration.TotalMinutes) minute(s) and $([int]$Duration.Seconds) second(s)"

5) Testing
Testing the functionality of your code can be done in the runbook editor. Click on the Test pane button above your script. After that you need to fill in the parameters and hit the Start button to execute the script.

Testing your script

6) Scheduling Runbook
To schedule your runbook in Azure Automation you first need to publish it via the Runbook editor. After it has been published you can add a schedule to this runbook.

• Edit the script in the runbook editor
• Click on publish (the editor will close and you will be redirected to the overview page)
• In the overview page click on Link to schedule
• In the Schedule menu you can select an existing schedule or create a new one
• In the Parameter menu you can provide the value for the parameters

Add schedule a runbook

Note: If you have multiple Azure Virtual Machines that you all want to start on the same time then you have a slight problem because you cannot reuse a schedule for the same runbook multiple times with different parameters (please upvote or add a comment). Workarounds:

1. create multiple identical schedules (ugly but works)
2. do everything in one big script (less flexible but works)

Log of runbook executions (start VM on weekdays)

Summary
In this post you saw how you can start an Azure Virtual Machine with PowerShell because the menu only supports auto-shutdown. With just a few lines of code and a schedule you can accomplish an auto-startup.
If you also add a webhook to this runbook then you could call this runbook with PowerShell code from other applications. For example Microsoft Automate (Flow) or a Power App on your mobile. In a future blog post we will show that.