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