Move Azure SQL Managed Instance across the virtual networks

In this blog that is continuation of #sqlmiops series, we will present a feature for moving Azure SQL Managed Instance from one to another subnet located in a different virtual network. This capability comes as an enhancement of the existing capability for moving the instance to another subnet.

The experience and set of commands that can be used for executing the move are the same as when going between subnets located in the same virtual network.

Readiness of the destination subnet

• Destination subnet must be empty or have SQL Managed Instances with same DNS zone as the instance being moved,
• Destination subnet must be ready for managed instance:
  • have network security group and route table assigned,
  • be delegated to SQL Managed Instance resource provider,

For more detailed explanation of subnet states read about mobility across the subnets.

How to move the instance

A cross-subnet instance move is part of the instance update operation. Existing commands for updating instance using API, Azure PowerShell, and Azure CLI have been enhanced with a subnet ID property.

The official documentation page provides a step-by-step article on how to move Azure SQL Managed Instance across subnets. Here, we will briefly cover examples using Azure portal UI and PowerShell.

Azure Portal

The option to choose the instance subnet is located on the Networking blade of the Azure portal. The instance move operation starts when you select a subnet and save your changes.

The first step of the move operation is to prepare the destination subnet for deployment, which may take several minutes. Once the subnet is ready, the instance move management operation starts and becomes visible in the Azure portal on the Overview page.

PowerShell

Use the Azure PowerShell command Set-AzSqlInstance to move an instance after you create your subnet in the same virtual network as your destination subnet. If you want to use an existing subnet, provide that subnet name in the PowerShell command.

Note: Part 2 of the example in this section prepares the destination subnet for instance deployment and moves the managed instance. If your destination subnet already has instances deployed to it, skip part 2.

###
### PART 1 – Define parameters

#Generating basic parameters
$subscriptionID = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
$sqlMIResourceGroupName = 'rg-name'      
$sqlMIName = 'contoso-mi'

$currentSubnetResourceGroupName = 'vnet-subnet-rg-name'
$currentVnetName = 'my-current-vnet'

$destinationSubnetResourceGroupName = 'my-destination-subnet-rg'
$destinationVnetName = 'my-destination-vnet'
$destinationSubnetName = 'my-destination-subnet'


### PART 2 – Prepare destination subnet

#Loading the url of script used for preparing the subnet for SQL MI deployment
$scriptUrlBase = 'https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/samples/manage/azure-sql-db-managed-instance/delegate-subnet'

#Generating destination subnet parameters
$parameters = @{
    subscriptionId = $subscriptionID
    resourceGroupName = $destinationSubnetResourceGroupName
    virtualNetworkName = $destinationVnetName
    subnetName = $destinationSubnetName
}

#Initiating subnet preparation script
Invoke-Command -ScriptBlock ([Scriptblock]::Create((iwr ($scriptUrlBase+'/delegateSubnet.ps1?t='+ [DateTime]::Now.Ticks)).Content)) -ArgumentList $parameters

### PART 3 – Peer virtual networks
$currentVnet = Get-AzVirtualNetwork -Name $currentVnetName -ResourceGroupName $currentSubnetResourceGroupName
$destinationVnet = Get-AzVirtualNetwork -Name $destinationVnetName -ResourceGroupName $destinationSubnetResourceGroupName

Add-AzVirtualNetworkPeering -Name source-to-destination -VirtualNetwork $currentVnet -RemoteVirtualNetworkId $destinationVnet.Id
Add-AzVirtualNetworkPeering -Name destination-to-source -VirtualNetwork $destinationVnet -RemoteVirtualNetworkId $currentVnet.Id

###
### PART 4 – Move instance to the new subnet

### Optional - If you want to change hardware generation at the same time, just specity that parameter as part of Set-AzSqlInstance
# -ComputeGeneration G8IM

# Standard-series (Gen5) -> Gen5
# Premium-series -> G8IM
# Memory optimized premium-series -> G8IH

### Optional - If you want to change number of vCores used, just specity that parameter as part of Set-AzSqlInstance
# -VCore 4

#Starting the cross-subnet instance move
Write-Host "Initiating instance move to the destination subnet." -ForegroundColor Yellow

Set-AzSqlInstance -Name $sqlMIName -ResourceGroupName $sqlMIResourceGroupName -SubnetId "/subscriptions/$subscriptionID/resourceGroups/$destinationSubnetResourceGroupName/providers/Microsoft.Network/virtualNetworks/$destinationVnetName/subnets/$destinationSubnetName" -AsJob -Force

After the instance move operation is completed, remove the peering between virtual networks if it is not required for some other scenario. From the perspective of instance move, bi-directional peering is required only during the instance move operation.

Summary

In this article, we introduced an enhancement for moving the instances across the subnets that are located in different virtual networks. Instance move (same as vCores scaling, hardware generation change, and other management operations) is operation with minimal downtime. Instance is available during operation except a short downtime caused by the failover that happens at the end of the update.

Stay tuned with #sqlmiops!