Auto Scale Azure Database for MySQL Using Azure Automation & PowerShell
Identify the load and scaling up or downgrading your compute capacity is critical for business and to achieve it requires manual intervention & downtime. On the other hand, continuous efforts\resources needed to monitor the environment. In the managed database scenario, most of the services already offering the auto scale\down capability in-built. In the case of the Azure Database for MySQL, we can configure the autoscale capability from the storage front however compute require manual intervention to achieve the same by increasing the SKU tiers or automate the whole process.
To simplify the process, I’m using the Azure Automation account, Automation Runbook, and PowerShell, explained the whole process step by step below.
Steps:
- Create Azure Automate Account from the Azure portal.
- As we’re accessing a couple of Azure PowerShell modules to access Azure accounts (AZ. Accounts) and MySQL (AZ. MySQL) resources, by default those modules are not incorporated hence will have to import it directly from the Module gallery.
- In the next step, we require Runbook where will have to define the flow or script down the complete process for Scale up\down (To ease I have created the two different rulebooks for both Scales up & down).
While creating, have to choose the mode which you want to leverage (In my case I have selected PowerShell, however you’re open to select Python as well).
- Now ready to show your coding skill, however, I included the basic code into the runbook console for changing your SKU values or Scaling up.
########################################################
# 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=$False,Position=2)]
[ValidateLength(1,100)]
[string]$SkuTier
)
# Track the execution date & time
$StartDate=(GET-DATE)
########################################################
# Log-in to Azure with AZ (standard code)
########################################################
Write-Verbose -Message 'Connecting to Azure'
# Name of the Azure Run As a connection
$ConnectionName = 'AzureRunAsConnection'
try
{
# Get the connection properties to connect Azure resources
$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)
{
#Missed to 'Create Azure Run As account'
$ErrorMessage = "Connection $ConnectionName not found."
throw $ErrorMessage
}
else
{
# additional execption
Write-Error -Message $_.Exception.Message
throw $_.Exception
}
}
########################################################
# Setting SKU Tier for MySQLDatabase
########################################################
$CurrentSKU = (Get-AzMySqlServer -Name $ServerName -ResourceGroupName $ResourceGroupName).skuname
If($CurrentSKU -eq $SkuTier)
{
# Validating the existing SKU value
Write-Error "Cannot change pricing tier of $ServerName because the new SKU $SkuTier tier is equal to current SKU tier $CurrentSKU."
return
}
else
{
try
{
# updating the existing SKU value with the new one
Update-AzMySqlServer -Name "$($ServerName)" -ResourceGroupName "$($ResourceGroupName)" -sku "$($SkuTier)"
}
catch
{
Write-Error -Message $_.Exception.Message
throw $_.Exception
}
}
Save and Test by entering the required parameters as mentioned below.
- Now your code is ready to publish and linked with MySQL Alerts.
- Go To MySQL resource and create a new alert rule
- Choose the appropriate signal type (In my case I have selected CPU percent and defined the threshold value to 70%)
|
|
|
- Create Action Group and select the user-defined Automation runbook and select the one which we created above from the runbook list.
- Please specify the required parameter in the parameter selection and complete.
|
|
- Lastly, specify the alert description and severity which will help to identify when this alert got triggered and action performed.
|
|
Conclusion
The above solution will help to identify the CPU spike\threshold and take appropriate action based on the steps defined into the Azure Automation runbook. Also, help to track the changes as part of the alert for auditing purpose. This solution helps to reduce the upfront cost (which occurs by procuring the tier tiers\sku’s resources) and take proactive action when identifying the under-utilized or high-utilized CPU; accordingly, Scale up\down based on the alert configuration.
