First published on MSDN on May 31, 2018
Azure SQL 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 Azure SQL 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 storage size for the instance. You don't need to specify both values - you can change just number of cores or just max storage 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 Azure SQL Managed Instance using this command. Syntax of the command is shown below (description can be found with the command
man Set-AzureRmSqlInstance
):
Set-AzureRmSqlInstance [-InputObject] <AzureSqlManagedInstanceModel> [-AdministratorPassword
<SecureString>] [-AssignIdentity] [-DefaultProfile <IAzureContextContainer>] [-Force] [-LicenseType <String>]
[-StorageSizeInGB <Int32>] [-Tag <Hashtable>] [-VCore <Int32>] [-Confirm] [-WhatIf] [<CommonParameters>]
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 storage 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"
