Change size of Azure SQL Managed Instance using PowerShell

First published on MSDN on May 31, 2018

Managed Instance is fully-managed SQL Server Database Engine hosted in Azure cloud. With Managed Instance you can easily add/remove cores associated to the instance and change the reserved size of the instance. You can use PowerShell to easily manage size of the instance and automate this process.

As a prerequisite, you need to have PowerShell libraries to configure Managed Instance. You would need to

install Azure RM PowerShell

and  AzureRm.Sql module that contains the commands for updating properties of Managed Instance.

In most of the cases, the following three commands might install everything that you need:

Install-Module PowerShellGet -Force

Install-Module -Name AzureRM -AllowClobber

Install-Module -Name AzureRM.Sql -Force

In some cases this version would not be loaded, so you would need to run something like:

Import-PackageProvider -Name PowerShellGet -Force -RequiredVersion 1.6.0

Sometime you might have version collision with the current version of PowerShellGet, so you might need to uninstall previous version (note that old version might be installed in Program Files, Program Files (x86) or both places so if you try to physically delete is it might be referenced on another place).

Also, you will need to create an Azure SQL Managed Instance. Take a look at

create managed instance post

if you still don't have and instance.

Then, you need to run something like the following PowerShell script :

Connect-AzureRmAccount

$subId = “8cmb8b62-bed6-4713-89ad-18497f75af51”

$resourceGroup = “my_managed_instances”

$instanceName = “jovanpop-managed-instance-gp”

Select-AzureRmSubscription -SubscriptionId $subId

$vCores = 16

$size = 512

Set-AzureRmSqlInstance `

-Name $instanceName `

-ResourceGroupName $resourceGroup `

-VCore $vCores `

-StorageSizeInGB $size

In this example, after connecting to my azure account, I'm selecting the subscription where my managed instance
jovanpop-managed-instance-gp
is placed and setting resource group name.

Then, I need to execute command
Update-AzureRmSqlManagedInstance
from AzureRm.Sql module, provide instance name and resource group where the instance is placed, and number of vCores and max size for the instance. You don't need to specify both values – you can change just number of cores or just max size limit.

The change that you made might break connection to your instance because the instance might be moved to the new location. If you are implementing

retry-logic in your data access code

you don't need to stop your workload while Managed Instance is changing the resources. You can continue running your workload while the change is happening.

You can update other properties of Managed Instance using this command. Syntax of the command is shown below (description can be found with  the command

man Set-AzureRmSqlInstance

):





Updating instance using core Azure RM

Azure Rm Sql has a set of utility methods that enable you easily work with Managed Instance. However you can do the same thing with core Azure RM library that don't have commands specialized for Azure SQL.

You can use the following script to update your instance using

Set-AzureRmResource

command without AzureRmSql library:

$properties = New-Object System.Object

$properties | Add-Member -type NoteProperty -name administratorLogin -Value $miAdminSqlLogin

$properties | Add-Member -type NoteProperty -name administratorLoginPassword -Value $miAdminSqlPassword

$properties | Add-Member -type NoteProperty -name vCores -Value $vCores

$properties | Add-Member -type NoteProperty -name storageSizeInGB -Value $maxStorage

$properties | Add-Member -type NoteProperty -name licenseType -Value $license

Set-AzureRmResource -Properties $properties -ResourceName $instanceName `

-ResourceType “Microsoft.SQL/managedInstances” -Sku $sku `

-ResourceGroupName $resourceGroup -Force -ApiVersion “2015-05-01-preview”

A simple example that changes the size to 1TB of managed instance is shown below:

$subId = “70b3d058-a51a-****-****-**********”

$resourceGroup = “my-resource-group”

$instanceName = “my-instance”

Select-AzureRmSubscription -SubscriptionId $subId

$properties = New-Object System.Object

$properties | Add-Member -type NoteProperty -name storageSizeInGB -Value 1024

Set-AzureRmResource -Properties $properties -ResourceName $instanceName `

-ResourceType “Microsoft.SQL/managedInstances” -Sku $sku `

-ResourceGroupName $resourceGroup -Force -ApiVersion “2015-05-01-preview”

 

This article was originally published by Microsoft's Azure SQL Database Blog. You can find the original article here.