{"id":6976,"date":"2021-07-06T16:17:16","date_gmt":"2021-07-06T10:47:16","guid":{"rendered":"https:\/\/opstree.com\/blog\/\/?p=6976"},"modified":"2021-07-06T16:22:37","modified_gmt":"2021-07-06T10:52:37","slug":"autoscale-azure-mysql-server-using-azure-automation-account-and-powershell","status":"publish","type":"post","link":"https:\/\/opstree.com\/blog\/2021\/07\/06\/autoscale-azure-mysql-server-using-azure-automation-account-and-powershell\/","title":{"rendered":"Autoscaling Azure MySql Server using Azure Automation"},"content":{"rendered":"\n

Azure automation is a cloud service that helps in automating management tasks thereby saving time and preventing errors. We can also use Azure automation for several other tasks in the cloud as well, for on-premises configuration items. Don’t wait for a traffic spike to take down your app or site. With Scheduled AutoScale, you can respond before anything happens. <\/p>\n\n\n\n

We had a standalone MySQL server that was running in production. When the workload used to increase\/decrease, we had to manually increase\/decrease the SKU tier. The process seemed hectic and time-consuming. That’s why we thought of autoscaling the MySQL server which led to better performance of the MySQL server and saved our time.<\/p>\n\n\n\n

In this blog, I have explained how to set up an Azure automation account, create a runbook, publish a runbook, scheduling, etc.<\/p>\n\n\n\n\n\n\n\n

Identifying loads and increasing or decreasing your computing capacity is critical to business and requires manual intervention and downtime to achieve. On the other hand, monitoring the environment requires continuous effort\/resources.<\/p>\n\n\n\n

In a managed database scenario, most services already provide built-in auto- scale down capability. In the case of Azure Database for MySQL, we can configure the autoscale capability from the storage front, however, the computation requires manual intervention to achieve this by increasing the SKU levels or automating the entire process.<\/p>\n\n\n\n

To simplify the process, I am using Azure Automation account, Automation Runbook and PowerShell, and the whole process is explained step by step below.<\/p>\n\n\n\n

Step-1: <\/strong>Create Azure Automation Account from the Azure portal for autoscaling the MySQL server.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

Step-2: <\/strong>Since we are accessing Azure accounts (AZ. Accounts) and some Azure PowerShell modules to access MySQL (AZ. MySQL) resources, those modules are not included by default, so it can be accessed directly from the module gallery and will have to be imported.<\/p>\n\n\n\n

You can view the “Module gallery” by clicking on the option under the “Shared Resource” section as shown.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

AZ. Accounts module:<\/strong> Manages credentials and common configuration for all Azure modules. Connect to Azure with an authenticated account for use with cmdlets from the Az PowerShell modules.<\/p>\n\n\n\n

AZ. MySQL module:<\/strong> Gets information about a server.<\/p>\n\n\n\n

Step-3: <\/strong>In the next step, we need a runbook where we have to define the flow or script below the whole process for scale-up and down (conveniently I have created two separate rule books for both scale up and down).<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

I have chosen PowerShell as a runbook type, however, there are multiple other options like Python, Graphical, etc that can be used.<\/p>\n\n\n\n

Step-4: <\/strong>I’ve included native code in the runbook console for changing or scaling your SKU values.<\/p>\n\n\n\n

### Parameters ###\n\n[CmdletBinding()]\nparam(\n\u00a0\u00a0\u00a0\u00a0[Parameter(Mandatory=$True)]\n\u00a0\u00a0\u00a0\u00a0[string]$ResourceGroupName,\n\u00a0\u00a0\u00a0\u00a0[Parameter(Mandatory=$True)]\n\u00a0\u00a0\u00a0\u00a0[string]$ServerName,\n\u00a0\u00a0\u00a0\u00a0[Parameter(Mandatory=$False)]\n\u00a0\u00a0\u00a0\u00a0[string]$SkuTier\n)\n\n# Track the execution date & time\n$StartDate=(GET-DATE)\n\n### Log-in to Azure with AZ (standard code) ###\n\nWrite-Verbose -Message 'Connecting to Azure'\n# Name of the Azure Run As a connection\n$connectionName = \"AzureRunAsConnection\"\ntry\n{\n\u00a0\u00a0\u00a0\u00a0# Get the connection \"AzureRunAsConnection \"\n\u00a0\u00a0\u00a0\u00a0$servicePrincipalConnection=Get-AutomationConnection -Name $connectionName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0Connect-AzAccount `\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0-ServicePrincipal `\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0-TenantId $servicePrincipalConnection.TenantId `\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0-ApplicationId $servicePrincipalConnection.ApplicationId `\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint\u00a0\n}\ncatch {\n\u00a0\u00a0\u00a0\u00a0if (!$servicePrincipalConnection)\n\u00a0\u00a0\u00a0\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$ErrorMessage = \"Connection $connectionName not found.\"\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0throw $ErrorMessage\n\u00a0\u00a0\u00a0\u00a0}\u00a0\n\u00a0\u00a0\u00a0\u00a0else {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Write-Error -Message $_.Exception\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0throw $_.Exception\n\u00a0\u00a0\u00a0\u00a0}\n}\n\n### Setting SKU Tier for MySQLDatabase ###\n\n$CurrentSKU = (Get-AzMySqlServer -Name $ServerName -ResourceGroupName $ResourceGroupName).skuname\nIf($CurrentSKU -eq $SkuTier)\n{\n\u00a0\u00a0\u00a0\u00a0# Validating the existing SKU value\n\u00a0\u00a0\u00a0\u00a0Write-Error \"Cannot change pricing tier of $ServerName because the new SKU $SkuTier tier is equal to current SKU tier $CurrentSKU.\"\n\u00a0\u00a0\u00a0\u00a0return\n}\nelse\n{\n\u00a0\u00a0\u00a0\u00a0try\n\u00a0\u00a0\u00a0\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0# updating the existing SKU value with the new one\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Update-AzMySqlServer -Name \"$($ServerName)\" -ResourceGroupName\u00a0 \"$($ResourceGroupName)\" -sku \"$($SkuTier)\"\n\u00a0\u00a0\u00a0\u00a0}\n\u00a0\u00a0\u00a0\u00a0catch\n\u00a0\u00a0\u00a0\u00a0{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Write-Error -Message $_.Exception.Message\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0throw $_.Exception\n\u00a0\u00a0\u00a0\u00a0}\u00a0\u00a0\u00a0\u00a0\n}\n<\/pre>\n\n\n\n
In this code, we have defined mandatory parameters for configuring azure resources for the MySql server.<\/p>\n\n\n\n
We are using cmdlets for managing Azure resources directly from the PowerShell command line. Azure PowerShell is designed to make it easy to learn and get started with but provides powerful features for automation. <\/p>\n\n\n\n
Log-in to Azure with AZ (standard code)<\/strong><\/p>\n\n\n\n
Try to connect azure with the help of Az.Accounts module and if servicePrincipalConnection is not found then throw the error message else write error .exception and throw the .exception.<\/p>\n\n\n\n
Setting SKU Tier for MySQLDatabase<\/strong><\/p>\n\n\n\n
Using the Az.mysqlserver module gets the information about a server for example servername, resourcegroupname etc. If the CurrentSKU value is equal to the SkuTier then cannot change pricing tier of the server because the new SKU tier is equal to the current SKU tier. But CurrentSKU value is not equal to the SkuTier then updating the existing SKU value with the new one.<\/p>\n\n\n\n
Step-5: <\/strong>Save and test by entering the required parameters as mentioned below.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Step-6: <\/strong>Once you’re done with the review, click on the “Publish” option to publish the runbook. Click on “Yes” to confirm.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Step-7: <\/strong>Your code is now ready to be published and linked with MySQL Alerts.<\/p>\n\n\n\n
  1. Go To MySQL resource and create a new alert rule<\/li>
  2. Choose the appropriate signal type (In my case I have selected CPU percent and defined the threshold value to 80%)<\/li><\/ol>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Step-8: <\/strong>Create Action Group and select User Defined Automation Runbook and select the one created above from the Runbook list.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Step-9: <\/strong>Please specify the required parameter in the parameter selection and complete.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Step-10: <\/strong>Configure the runbook and save after specifying the required parameter in the parameter selection. <\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    Step-11: <\/strong>Finally, specify the alert description and severity that will help identify when this alert was triggered and take action.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Step-12:<\/strong> With all these steps, we increased the SKU tier and achieved our task.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Conclusion:<\/strong><\/p>\n\n\n\n
    The MySQL server was running in production and the workloads led to an increase in the CPU utilization and the same was decreasing when the workload was reduced. <\/p>\n\n\n\n
    I tried searching for a solution to this scenario but there were not enough resources present online and so I decided to explore the service and checked how to increase\/decrease SKU whenever required. <\/p>\n\n\n\n
    The above solution will help to identify the CPU threshold and take appropriate action based on the steps defined in Azure Automation Runbook. Also, it will help in tracking the changes as part of alerts for auditing purposes. This solution helps to reduce the upfront cost (which results from purchasing tier\\sku’s resources) and take proactive action when identifying low-usage or high-usage CPUs; accordingly, the alert scales up\\down depending on the configuration.<\/p>\n\n\n\n
     Blog Pundit:<\/strong>   Kapendra Singh<\/strong><\/a> and Adeel<\/strong> Ahmad<\/strong><\/a><\/p>\n\n\n\n
    Opstree is an End to End DevOps solution provider<\/p>\n\n\n\n
    \n
    CONTACT US<\/a><\/div>\n<\/div>\n\n\n\n
    Connect Us <\/strong><\/p>\n\n\n\n