Blog

Auto scale up/down the Managed instance

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

No Comments

Leave a Reply

How to test for hypoglycemiaGetting Started with Databases: SQL and Data Visualizations Basics

Contact Us

We are always here to help. Please reach out to us and we'll get back to you as soon as possible.

Address:

Maruti Court, Ground Floor
Mvuli Road, Nairobi, Kenya

Contacts:

+254716973110

office@digitalexperts.co.ke

Hours:

Mon-Fri 8am – 5pm
Sat 9am – 1am
Sun & Holidays Closed