%3CLINGO-SUB%20id%3D%22lingo-sub-1955740%22%20slang%3D%22en-US%22%3EAuto%20Stop%20and%20Start%20your%20Azure%20Database%20for%20MySQL%20Single%20Server%20using%20PowerShell%20runbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1955740%22%20slang%3D%22en-US%22%3E%3CP%3EMost%20of%20the%20developers%20may%20want%20to%20stop%20their%20MySQL%20instance%20over%20the%20weekend%20or%20by%20end%20of%20business%20day%20after%20development%20or%20testing%20to%20stop%20billing%20and%20save%20cost.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAzure%20Database%20for%20MySQL%20Single%20Server%20%26amp%3B%20Flexible%20Server%20(in%20Preview)%20supports%20stop%2Fstart%20of%20standalone%20mysql%20server.%20These%20operations%20are%20not%20supported%20for%20servers%20which%20are%20involved%20in%20replication%20using%20read%20replica%20feature.%20Once%20you%20stop%20the%20server%2C%20you%20will%20not%20be%20able%20to%20run%20any%20other%20management%20operation%20by%20design.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20blog%20post%2C%20I%20will%20share%20how%20can%20you%20use%20PowerShell%20Automation%20runbook%20to%20stop%20or%20start%20your%20Azure%20Database%20for%20MySQL%20Single%20Server%20automatically%20based%20on%20time-based%20schedule.%20While%20the%20example%20below%20is%20for%20Single%20Server%2C%20the%20concept%20can%20be%20easily%20be%20extended%20to%20Flexible%20Server%20deployment%20option%20too%20when%20PowerShell%20support%20is%20available.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFollowing%20is%20high-level%20idea%20of%20performing%20stop%2Fstart%20on%20Azure%20Database%20for%20MySQL%20Single%20Server%20using%20REST%20API%20call%20from%20PowerShell%20runbook.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image01_HighLevel.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237688iC90E3B0528763B06%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image01_HighLevel.PNG%22%20alt%3D%22Image01_HighLevel.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EI%20will%20be%20using%20Azure%20Automation%20account%20to%20schedule%20the%20runbook%20to%20stop%2Fstart%20our%20Azure%20Database%20MySQL%20Single%20Server.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20the%20steps%20below%20to%20achieve%20this%20task%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E1.%20Navigate%20to%20the%20%3CSTRONG%3EAzure%20Automation%20Account%3C%2FSTRONG%3E%20and%20make%20sure%20that%20you%20have%20%3CSTRONG%3EAz.Accounts%3C%2FSTRONG%3E%20module%20imported.%20If%20it%E2%80%99s%20not%20available%20in%20modules%2C%20then%20import%20the%20module%20first%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image02.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237689iC09C57D79A99C5EA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image02.PNG%22%20alt%3D%22Image02.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2.%20Select%20%3CSTRONG%3ERunbooks%3C%2FSTRONG%3E%20blade%20and%20create%20a%20new%20PowerShell%20runbook.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image03.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237691i6C55034A79C24BF7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image03.PNG%22%20alt%3D%22Image03.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E3.%20Copy%20%26amp%3B%20Paste%20the%20following%20PowerShell%20code%20to%20newly%20created%20runbook.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3E%23Envrionment%20parameters%0Aparam(%20%0A%0A%5Bparameter(Mandatory%3D%24true)%5D%20%0A%5Bstring%5D%20%24resourceGroupName%2C%20%0A%20%0A%5Bparameter(Mandatory%3D%24true)%5D%20%0A%5Bstring%5D%20%24serverName%2C%20%0A%20%20%0A%20%0A%5Bparameter(Mandatory%3D%24true)%5D%20%0A%5Bstring%5D%20%24action%0A)%20%0A%20%0Afilter%20timestamp%20%7B%22%5B%24(Get-Date%20-Format%20G)%5D%3A%20%24_%22%7D%20%0A%20%0AWrite-Output%20%22Script%20started.%22%20%7C%20timestamp%20%0A%20%0A%23%24VerbosePreference%20%3D%20%22Continue%22%20%23%23enable%20this%20for%20verbose%20logging%0A%24ErrorActionPreference%20%3D%20%22Stop%22%20%0A%20%0A%23Authenticate%20with%20Azure%20Automation%20Run%20As%20account%20(service%20principal)%20%0A%24connectionName%20%3D%20%22AzureRunAsConnection%22%0Atry%0A%7B%0A%20%20%20%20%23%20Get%20the%20connection%20%22AzureRunAsConnection%20%22%0A%20%20%20%20%24servicePrincipalConnection%3DGet-AutomationConnection%20-Name%20%24connectionName%20%20%20%20%20%20%20%20%20%0A%0A%20%20%20%20%22Logging%20in%20to%20Azure...%22%0A%20%20%20%20Add-AzAccount%20%60%0A%20%20%20%20%20%20%20%20-ServicePrincipal%20%60%0A%20%20%20%20%20%20%20%20-TenantId%20%24servicePrincipalConnection.TenantId%20%60%0A%20%20%20%20%20%20%20%20-ApplicationId%20%24servicePrincipalConnection.ApplicationId%20%60%0A%20%20%20%20%20%20%20%20-CertificateThumbprint%20%24servicePrincipalConnection.CertificateThumbprint%20%7C%20Out-Null%20%0A%7D%0Acatch%20%7B%0A%20%20%20%20if%20(!%24servicePrincipalConnection)%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%24ErrorMessage%20%3D%20%22Connection%20%24connectionName%20not%20found.%22%0A%20%20%20%20%20%20%20%20throw%20%24ErrorMessage%0A%20%20%20%20%7D%20else%7B%0A%20%20%20%20%20%20%20%20Write-Error%20-Message%20%24_.Exception%0A%20%20%20%20%20%20%20%20throw%20%24_.Exception%0A%20%20%20%20%7D%0A%7D%0AWrite-Output%20%22Authenticated%20with%20Automation%20Run%20As%20Account.%22%20%20%7C%20timestamp%20%0A%20%0A%24startTime%20%3D%20Get-Date%20%0AWrite-Output%20%22Azure%20Automation%20local%20time%3A%20%24startTime.%22%20%7C%20timestamp%20%0A%0A%0A%23%20Get%20the%20authentication%20token%20%0A%24azContext%20%3D%20Get-AzContext%0A%24azProfile%20%3D%20%5BMicrosoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider%5D%3A%3AInstance.Profile%0A%24profileClient%20%3D%20New-Object%20-TypeName%20Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient%20-ArgumentList%20(%24azProfile)%0A%24token%20%3D%20%24profileClient.AcquireAccessToken(%24azContext.Subscription.TenantId)%0A%24authHeader%20%3D%20%40%7B%0A%20%20%20%20'Content-Type'%3D'application%2Fjson'%0A%20%20%20%20'Authorization'%3D'Bearer%20'%20%2B%20%24token.AccessToken%0A%7D%0AWrite-Output%20%22Authentication%20Token%20acquired.%22%20%7C%20timestamp%20%0A%0A%23%23Invoke%20REST%20API%20Call%20based%20on%20specified%20action%0A%0Aif(%24action%20-eq%20'stop')%0A%7B%0A%0A%20%20%20%20%20%20%20%20%23%20Invoke%20the%20REST%20API%0A%20%20%20%20%20%20%20%20%24restUri%3D'https%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F6ff855b5-ee6d-4bc2-a901-adf5569842e1%2FresourceGroups%2F'%2B%24resourceGroupName%2B'%2Fproviders%2FMicrosoft.DBForMySQL%2Fservers%2F'%2B%24serverName%2B'%2F'%2B%24action%2B'%3Fapi-version%3D2020-01-01'%0A%20%20%20%20%20%20%20%20%24response%20%3D%20Invoke-RestMethod%20-Uri%20%24restUri%20-Method%20POST%20-Headers%20%24authHeader%0A%20%20%20%20%20%20%20%20Write-Output%20%22%24servername%20is%20getting%20stopped.%22%20%7C%20timestamp%20%0A%7D%0Aelse%0A%7B%0A%20%20%20%20%20%20%20%20%23%20Invoke%20the%20REST%20API%0A%20%20%20%20%20%20%20%20%24restUri%3D'https%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F6ff855b5-ee6d-4bc2-a901-adf5569842e1%2FresourceGroups%2F'%2B%24resourceGroupName%2B'%2Fproviders%2FMicrosoft.DBForMySQL%2Fservers%2F'%2B%24serverName%2B'%2F'%2B%24action%2B'%3Fapi-version%3D2020-01-01'%0A%20%20%20%20%20%20%20%20%24response%20%3D%20Invoke-RestMethod%20-Uri%20%24restUri%20-Method%20POST%20-Headers%20%24authHeader%0A%20%20%20%20%20%20%20%20Write-Output%20%22%24servername%20is%20Starting.%22%20%7C%20timestamp%20%0A%20%7D%0A%0AWrite-Output%20%22Script%20finished.%22%20%7C%20timestamp%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4.%20Save%20the%20runbook%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image04.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237690iF55B94C377F47D59%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image04.PNG%22%20alt%3D%22Image04.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E5.%20Publish%20the%20PowerShell%20runbook%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image05.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237692i684683E0429635F5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image05.PNG%22%20alt%3D%22Image05.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E6.%20Test%20the%20PowerShell%20runbook%20by%20entering%20mandatory%20field.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image06.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237694i779DD92CA9CFD8CD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image06.PNG%22%20alt%3D%22Image06.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E7.%20Verify%20the%20job%20output%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image07.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237693iCFEA8ED0C9AA7416%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image07.PNG%22%20alt%3D%22Image07.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E8.%26nbsp%3BNow%20we%20have%20seen%20that%20Runbook%20worked%20as%20expected%2C%20lets%20add%20the%20schedule%20to%20stop%20and%20start%20Azure%20Database%20for%20MySQL%20Single%20Server%20over%20the%20weekend.%20Go%20to%20%3CSTRONG%3EOverview%3C%2FSTRONG%3E%20tab%20and%20click%20on%20%3CSTRONG%3ELink%20to%20schedule%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image08.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237695i93491459F4CDE3A0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image08.PNG%22%20alt%3D%22Image08.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E9.%26nbsp%3BAdd%20schedule%20and%20runbook%20parameters.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image09.PNG%22%20style%3D%22width%3A%20577px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237696iFDEBF7FFC31BFB05%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image09.PNG%22%20alt%3D%22Image09.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E10.%20Add%20weekly%20stop%20schedule%20on%20every%20Friday%20night.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image10.PNG%22%20style%3D%22width%3A%20358px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237698i01097B02387395D2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image10.PNG%22%20alt%3D%22Image10.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E11.%20Add%20the%20parameters%20to%20stop%20Azure%20Database%20for%20MySQL%20Single%20Server.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image11.PNG%22%20style%3D%22width%3A%20474px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237697i4ED6B6C5878BF0B5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image11.PNG%22%20alt%3D%22Image11.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E12.%20Similarly%20add%20a%20weekly%20schedule%20for%20Starting%20Azure%20Database%20for%20MySQL%20Single%20Server%20on%20every%20Monday%20morning%20and%20verify%20these%20stop%2Fstart%20schedule%20by%20navigating%20to%20Runbook%E2%80%99s%20%3CSTRONG%3ESchedules%3C%2FSTRONG%3E%20blade.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image12.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237699i92C3D587A74B1884%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image12.PNG%22%20alt%3D%22Image12.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20activity%2C%20we%20have%20added%20two%20schedules%20to%20stop%20and%20start%20Azure%20Database%20for%20MySQL%20Single%20Server%20based%20on%20given%20action.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20this%20will%20be%20helpful%20to%20you%20to%20save%20cost%20and%20hope%20you%20enjoyed%20this%20learning%20!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1955740%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Image01_HighLevel.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237702iF3394776D3AA4B10%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Image01_HighLevel.PNG%22%20alt%3D%22Image01_HighLevel.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1955740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Database%20for%20MySQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2006962%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Stop%20and%20Start%20your%20Azure%20Database%20for%20MySQL%20Single%20Server%20using%20PowerShell%20runbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2006962%22%20slang%3D%22en-US%22%3E%3CP%3EGreat%20blog%20post%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F326124%22%20target%3D%22_blank%22%3E%40Shashikant_Shakya%3C%2FA%3E%26nbsp%3B.%20However%2C%20I%20am%20getting%20the%20following%20errors.%20Any%20help%20is%20appreciated%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFailed%3CBR%20%2F%3E%7B%22error%22%3A%7B%22code%22%3A%22InvalidAuthenticationTokenTenant%22%2C%22message%22%3A%22The%20access%20token%20is%20from%20the%20wrong%20issuer%20'%3CA%20href%3D%22https%3A%2F%2Fsts.windows.net%2Fbd008890-25bb-433d-a81c-bdc8d99c1dbf%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsts.windows.net%2Fbd008890-25bb-433d-a81c-bdc8d99c1dbf%2F%3C%2FA%3E'.%20It%20must%20match%20one%20of%20the%20tenants%20'%3CA%20href%3D%22https%3A%2F%2Fsts.windows.net%2F2f4a9838-26b7-47ee-be60-ccc1fdec5953%2F%2Chttps%3A%2F%2Fsts.windows.net%2F72f988bf-86f1-41af-91ab-2d7cd011db47%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsts.windows.net%2F2f4a9838-26b7-47ee-be60-ccc1fdec5953%2F%2Chttps%3A%2F%2Fsts.windows.net%2F72f988bf-86f1-41af-91ab-2d7cd011db47%2F%3C%2FA%3E'%20associated%20with%20this%20subscription.%20Please%20use%20any%20authority%20(URL)%20from%20'%3CA%20href%3D%22https%3A%2F%2Flogin.windows.net%2F2f4a9838-26b7-47ee-be60-ccc1fdec5953%2Chttps%3A%2F%2Flogin.windows.net%2F72f988bf-86f1-41af-91ab-2d7cd011db47%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Flogin.windows.net%2F2f4a9838-26b7-47ee-be60-ccc1fdec5953%2Chttps%3A%2F%2Flogin.windows.net%2F72f988bf-86f1-41af-91ab-2d7cd011db47%3C%2FA%3E'%20to%20get%20the%20token.%20Note%2C%20if%20the%20subscription%20is%20transferred%20to%20another%20tenant%20there%20is%20no%20impact%20to%20the%20services%2C%20but%20information%20about%20new%20tenant%20could%20take%20time%20to%20propagate%20(up%20to%20an%20hour).%20If%20you%20just%20transferred%20your%20subscription%20and%20see%20this%20error%20message%2C%20please%20try%20back%20later.%22%7D%7D%20(The%20remote%20server%20returned%20an%20error%3A%20(401)%20Unauthorized.)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2008255%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Stop%20and%20Start%20your%20Azure%20Database%20for%20MySQL%20Single%20Server%20using%20PowerShell%20runbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2008255%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F908609%22%20target%3D%22_blank%22%3E%40BigFoot2049%3C%2FA%3E%26nbsp%3BThe%20subscription%20ID%20in%20%24restUri%20needs%20to%20be%20updated%20with%20your%20subscription%20ID%20instead%20of%20the%20hardcoded%20subscription%20ID%20in%20the%20document.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20to%20one%20of%20my%20co-workers%20for%20helping%20me%20spot%20the%20problem%20today%20as%20I%20was%20having%20the%20exact%20same%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20substitute%20the%20value%20of%20SubscriptionId%20from%20the%20servicePrincipalConnection%20hash%20in%20%24restUri%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%24restUri%3C%2FSPAN%3E%3CSPAN%3E%3D%3C%2FSPAN%3E%3CSPAN%3E'%3CA%20href%3D%22https%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F%3C%2FA%3E%3CSTRONG%3E'%3C%2FSTRONG%3E%3C%2FSPAN%3E%3CSTRONG%3E%2B%24servicePrincipalConnection.SubscriptionId%2B%3C%2FSTRONG%3E%3CSPAN%3E%3CSTRONG%3E'%3C%2FSTRONG%3E%2FresourceGroups%2F'%3C%2FSPAN%3E%3CSPAN%3E%2B%3C%2FSPAN%3E%3CSPAN%3E%24resourceGroupName%3C%2FSPAN%3E%3CSPAN%3E%2B%3C%2FSPAN%3E%3CSPAN%3E'%2Fproviders%2FMicrosoft.DBForMySQL%2Fservers%2F'%3C%2FSPAN%3E%3CSPAN%3E%2B%3C%2FSPAN%3E%3CSPAN%3E%24serverName%3C%2FSPAN%3E%3CSPAN%3E%2B%3C%2FSPAN%3E%3CSPAN%3E'%2F'%3C%2FSPAN%3E%3CSPAN%3E%2B%3C%2FSPAN%3E%3CSPAN%3E%24action%3C%2FSPAN%3E%3CSPAN%3E%2B%3C%2FSPAN%3E%3CSPAN%3E'%3Fapi-version%3D2020-01-01'%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20you%20can%20use%20Az-Context%20and%20then%20substitute%20the%20Subscription.Id%20value%20into%20%24restUri%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24azContext%20%3D%20Get-AzContext%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24restUri%3D'%3CA%20href%3D%22https%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F%3C%2FA%3E%3CSTRONG%3E'%2B%24azContext.Subscription.Id%2B'%3C%2FSTRONG%3E%2FresourceGroups%2F'%2B%24resourceGroupName%2B'%2Fproviders%2FMicrosoft.DBForMySQL%2Fservers%2F'%2B%24serverName%2B'%2F'%2B%24action%2B'%3Fapi-version%3D2020-01-01'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEither%20way%20you're%20no%20longer%20hardcoding%20a%20subscription%20ID%20into%20the%20script.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2008402%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Stop%20and%20Start%20your%20Azure%20Database%20for%20MySQL%20Single%20Server%20using%20PowerShell%20runbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2008402%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F908609%22%20target%3D%22_blank%22%3E%40BigFoot2049%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F908924%22%20target%3D%22_blank%22%3E%40bofh2%3C%2FA%3E%26nbsp%3BThanks%20for%20pointing%20this%20out.%20We%20have%20made%20the%20changes%20in%20script.%3C%2FP%3E%3C%2FLINGO-BODY%3E

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.

Image01_HighLevel.PNG

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:

 

Image02.PNG

 

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

 

Image03.PNG

 

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/'+$azContext.Subscription.Id+'/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/'+$azContext.Subscription.Id+'/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

 

Image04.PNG

 

5. Publish the PowerShell runbook

 

Image05.PNG

 

6. Test the PowerShell runbook by entering mandatory field.

 

Image06.PNG

 

7. Verify the job output:

 

Image07.PNG

 

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

Image08.PNG

 

9. Add schedule and runbook parameters.

Image09.PNG

 

10. Add weekly stop schedule on every Friday night.

 

Image10.PNG

 

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

 

Image11.PNG

 

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.

 

Image12.PNG

 

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 !!!

3 Comments
Occasional Visitor

Great blog post @Shashikant_Shakya . However, I am getting the following errors. Any help is appreciated:

 

Failed
{"error":{"code":"InvalidAuthenticationTokenTenant","message":"The access token is from the wrong issuer 'https://sts.windows.net/bd008890-25bb-433d-a81c-bdc8d99c1dbf/'. It must match one of the tenants 'https://sts.windows.net/2f4a9838-26b7-47ee-be60-ccc1fdec5953/,https://sts.windows.net/72f988bf-86f1-...' associated with this subscription. Please use any authority (URL) from 'https://login.windows.net/2f4a9838-26b7-47ee-be60-ccc1fdec5953,https://login.windows.net/72f988bf-86...' to get the token. Note, if the subscription is transferred to another tenant there is no impact to the services, but information about new tenant could take time to propagate (up to an hour). If you just transferred your subscription and see this error message, please try back later."}} (The remote server returned an error: (401) Unauthorized.)

Visitor

@BigFoot2049 The subscription ID in $restUri needs to be updated with your subscription ID instead of the hardcoded subscription ID in the document. 

Thanks to one of my co-workers for helping me spot the problem today as I was having the exact same issue.

 

You can substitute the value of SubscriptionId from the servicePrincipalConnection hash in $restUri:

 

$restUri='https://management.azure.com/subscriptions/'+$servicePrincipalConnection.SubscriptionId+'/resourceGroups/'+$resourceGroupName+'/providers/Microsoft.DBForMySQL/servers/'+$serverName+'/'+$action+'?api-version=2020-01-01'

 

Or you can use Az-Context and then substitute the Subscription.Id value into $restUri:

 

$azContext = Get-AzContext

 

$restUri='https://management.azure.com/subscriptions/'+$azContext.Subscription.Id+'/resourceGroups/'+$resourceGroupName+'/providers/Microsoft.DBForMySQL/servers/'+$serverName+'/'+$action+'?api-version=2020-01-01'

 

Either way you're no longer hardcoding a subscription ID into the script.

@BigFoot2049 @bofh2 Thanks for pointing this out. We have made the changes in script.