Auto scale up/down the Managed instance

[Guest amanda_ibrahim]

Request:

 

If the customer needs to configure auto scaling for SQL Managed Instance, where the vCores can be increase and decrease automatically on specific time.

 

 

 

Solution:

 

You may take a look for this blog where it has a full example but for Azure SQL DB, you may customize it according to your needs:

How-to auto-scale Azure SQL Databases - Microsoft Tech Community

 

 

 

Or you can follow the solution provided below:

 

Find below the steps you need to follow to create a Runbook to change the properties of the Managed instance, kindly note that this is a sample to help you out to set a base for your request then you can customize it according to your business needs.

 

 

 

The PowerShell command to get the Managed Instance information and to update the Managed Instance properties are:

 

• Get-AzSqlInstance (Az.Sql) | Microsoft Docs
  
• Set-AzSqlInstance (Az.Sql) | Microsoft Docs
  

 

 

 

1- Create a runbook with below proprieties:

 

 

 

 

2- Be sure that the AZ.account Module is added to the Automation account:

 

 

 

 

3- Then copy and paste the below PowerShell code:

 

 

 

param(

[parameter(Mandatory=$false)]

[string] $environmentName = "AzureCloud",

 

[parameter(Mandatory=$true)]

[string] $resourceGroupName,

 

[parameter(Mandatory=$false)]

[string] $azureRunAsConnectionName = "AzureRunAsConnection",

 

[parameter(Mandatory=$true)]

[string] $MIName,

 

[parameter(Mandatory=$false)]

[string] $defaultEdition = "GeneralPurpose",

 

[parameter(Mandatory=$false)]

[string] $defaultvCores = "4",

 

[parameter(Mandatory=$false)]

[string] $defaultStorageSizeInGB = "32",

 

[parameter(Mandatory=$false)]

[string] $defaultComputeGeneration = "Gen5"

 

)

 

filter timestamp {"[$(Get-Date -Format G)]: $_"}

Write-Output "Script started." | timestamp

 

#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..."

Connect-AzAccount `

-ServicePrincipal `

-TenantId $servicePrincipalConnection.TenantId `

-ApplicationId $servicePrincipalConnection.ApplicationId `

-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint

 

Write-Output "Authenticated with Automation Run As Account." | timestamp

}

catch

{

if (!$servicePrincipalConnection)

{

$ErrorMessage = "Connection $connectionName not found."

throw $ErrorMessage

} else{

Write-Error -Message $_.Exception

throw $_.Exception

}

}

 

# Get the MI object

try

{

$sqlMI = Get-AzSqlInstance -Name $MIName -ResourceGroupName $ResourceGroupName

Write-Output "MI name: $($sqlMI.ManagedInstanceName)" | timestamp

if ($sqlMI.ManagedInstanceName -ne "")

{

Write-Output "Current MI vCores: $($sqlMI.VCores), Storage: $($sqlMI.StorageSizeInGB)" | timestamp

 

Write-Output "---> Scaling schedule found. Check if current edition/tier is matching..." | timestamp

if($sqlMI.VCores -ne $defaultvCores -or $sqlMI.StorageSizeInGB -ne $defaultStorageSizeInGB)

{

Write-Output "--- ---> vCores and/or Storage Size are different. Changing!" | timestamp

Set-AzSqlInstance -Name $MIName -ResourceGroupName $ResourceGroupName -VCore $defaultvCores -StorageSizeInGB $defaultStorageSizeInGB -ComputeGeneration $defaultComputeGeneration -Edition $defaultEdition -Force -Confirm:$false | out-null

 

Write-Output "Change to vCores/Storage size as specified in scaling schedule initiated..." | timestamp

 

$sqlMI = Get-AzSqlInstance -Name $MIName -ResourceGroupName $ResourceGroupName

Write-Output "Current MI vCores: $($sqlMI.VCores), Storage: $($sqlMI.StorageSizeInGB)" | timestamp

}

else

{

Write-Output "Current MI vCores and/or Sotrage size matches the scaling schedule already. Exiting..." | timestamp

}

}

else{

Write-Error "Could not retrieve MI details" | timestamp

}

}

catch

{

Write-Error -Message $_.Exception

throw $_.Exception

 

}

Write-Output "Script finished." | timestamp

 

 

 

 

 

4- Run the script and pass the values of the Resource group and Managed instance name on the below highlighted parameters:

 

 

 

 

5- You should have a result similar to the below if the operation finished successfully, also you should expect the MI to be updated:

 

 

 

 

 

 

6- You can schedule this Job following the steps on the below link:

 

Manage schedules in Azure Automation | Microsoft Docs

 

 

 

 

Note:

 

Please be aware that the scaling operation could take a while depending on the amount of data you have across all databases in the Managed Instance.

 

For more information refer to Management operations overview - Azure SQL Managed Instance | Microsoft Docs

 

Continue reading...