Auto Stop and Start your Azure Database for MySQL Single Server using PowerShell runbook

by Contributed | Dec 4, 2020 | Azure, Microsoft, Technology

This article is contributed. See the original author and article here.

Most of the developers may want to stop their MySQL instance over the weekend or by end of business day after development or testing to stop billing and save cost.

Azure Database for MySQL Single Server & Flexible Server (in Preview) supports stop/start of standalone mysql server. These operations are not supported for servers which are involved in replication using read replica feature. Once you stop the server, you will not be able to run any other management operation by design.

In this blog post, I will share how can you use PowerShell Automation runbook to stop or start your Azure Database for MySQL Single Server automatically based on time-based schedule. While the example below is for Single Server, the concept can be easily be extended to Flexible Server deployment option too when PowerShell support is available.

Following is high-level idea of performing stop/start on Azure Database for MySQL Single Server using REST API call from PowerShell runbook.

I will be using Azure Automation account to schedule the runbook to stop/start our Azure Database MySQL Single Server.

You can use the steps below to achieve this task:

1. Navigate to the Azure Automation Account and make sure that you have Az.Accounts module imported. If it’s not available in modules, then import the module first:

2. Select Runbooks blade and create a new PowerShell runbook.

3. Copy & Paste the following PowerShell code to newly created runbook.

#Envrionment parameters
param( 

[parameter(Mandatory=$true)] 
[string] $resourceGroupName, 
 
[parameter(Mandatory=$true)] 
[string] $serverName, 
  
 
[parameter(Mandatory=$true)] 
[string] $action
) 
 
filter timestamp {"[$(Get-Date -Format G)]: $_"} 
 
Write-Output "Script started." | timestamp 
 
#$VerbosePreference = "Continue" ##enable this for verbose logging
$ErrorActionPreference = "Stop" 
 
#Authenticate with Azure Automation Run As account (service principal) 
$connectionName = "AzureRunAsConnection"
try
{
    # Get the connection "AzureRunAsConnection "
    $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName         

    "Logging in to Azure..."
    Add-AzAccount `
        -ServicePrincipal `
        -TenantId $servicePrincipalConnection.TenantId `
        -ApplicationId $servicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint | Out-Null 
}
catch {
    if (!$servicePrincipalConnection)
    {
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
    }
}
Write-Output "Authenticated with Automation Run As Account."  | timestamp 
 
$startTime = Get-Date 
Write-Output "Azure Automation local time: $startTime." | timestamp 


# Get the authentication token 
$azContext = Get-AzContext
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$profileClient = New-Object -TypeName Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient -ArgumentList ($azProfile)
$token = $profileClient.AcquireAccessToken($azContext.Subscription.TenantId)
$authHeader = @{
    'Content-Type'='application/json'
    'Authorization'='Bearer ' + $token.AccessToken
}
Write-Output "Authentication Token acquired." | timestamp 

##Invoke REST API Call based on specified action

if($action -eq 'stop')
{

        # Invoke the REST API
        $restUri='https://management.azure.com/subscriptions/6ff855b5-ee6d-4bc2-a901-adf5569842e1/resourceGroups/'+$resourceGroupName+'/providers/Microsoft.DBForMySQL/servers/'+$serverName+'/'+$action+'?api-version=2020-01-01'
        $response = Invoke-RestMethod -Uri $restUri -Method POST -Headers $authHeader
        Write-Output "$servername is getting stopped." | timestamp 
}
else
{
        # Invoke the REST API
        $restUri='https://management.azure.com/subscriptions/6ff855b5-ee6d-4bc2-a901-adf5569842e1/resourceGroups/'+$resourceGroupName+'/providers/Microsoft.DBForMySQL/servers/'+$serverName+'/'+$action+'?api-version=2020-01-01'
        $response = Invoke-RestMethod -Uri $restUri -Method POST -Headers $authHeader
        Write-Output "$servername is Starting." | timestamp 
 }

Write-Output "Script finished." | timestamp

4. Save the runbook

5. Publish the PowerShell runbook

6. Test the PowerShell runbook by entering mandatory field.

7. Verify the job output:

8. Now we have seen that Runbook worked as expected, lets add the schedule to stop and start Azure Database for MySQL Single Server over the weekend. Go to Overview tab and click on Link to schedule

9. Add schedule and runbook parameters.

10. Add weekly stop schedule on every Friday night.

11. Add the parameters to stop Azure Database for MySQL Single Server.

12. Similarly add a weekly schedule for Starting Azure Database for MySQL Single Server on every Monday morning and verify these stop/start schedule by navigating to Runbook’s Schedules blade.

In this activity, we have added two schedules to stop and start Azure Database for MySQL Single Server based on given action.

I hope this will be helpful to you to save cost and hope you enjoyed this learning !!!