In this blog, we will present a feature for moving Azure SQL Managed Instance from one subnet to another in an online way. This is a brand new feature that represents a step forward into the resource mobility area, and another contribution in the #sqlmiops space.
Introduction
Azure SQL Managed Instance must be deployed inside a dedicated subnet within an Azure virtual network. The number of managed instances that can be deployed within the subnet depends on the size of the subnet (subnet range).
Non-empty subnets have a static IP address range that cannot be changed. On the other hand, instance architecture relies on virtual clusters. As a result of instance management operations, clusters go through processes of expansion and shrinking of virtual machines forming it. This results in a dynamic behavior of IP address usage of managed instances deployed in the subnet. It is recommended to always determine required subnet size and range for Azure SQL Managed Instance, before starting deployments.
Use cases
There are various reasons why you may want to move an instance between subnets. These include:
- Moving the instance from one environment to another (from dev to prod, for example where prod and dev environments use different subnets).
- Initial IP address range planning did not take into account the service growth and there aren’t enough IP addresses to add a new instance.
- Inability to perform scaling operations (vCores, changing service tier, scaling storage of BC instances) due to lack of IP addresses in the subnet.
- Inability to upgrade instance hardware from Gen4 to Gen5 due to the lack of IP addresses in the subnet.
Overview
At its core, the operation of moving an instance from one subnet to another is quite similar to scaling the number of vCores or changing the service tier. During the move, Azure SQL Managed Instance remains available with the exception of a short downtime when the failover happens - typically lasting up to 10 seconds, even if long-running transactions are interrupted.
Moving the instance to another subnet triggers the following virtual cluster operations:
- The destination subnet builds out or resizes the virtual cluster.
- The virtual cluster is removed or defragmented in the source subnet.
The following table details operation steps that occur during the instance move operation:
| Step name | Step description |
| Request validation |
Validates the submitted parameters. If a misconfiguration is detected, the operation fails with an error. |
| Virtual cluster resizing / creation | Depending on the state of the destination subnet, the virtual cluster is either created or resized. |
| New instance startup | The SQL process starts on deployed virtual cluster in the destination subnet. |
| Seeding database files / attaching database files | Depending on the service tier, either databases are seeded, or database files are attached. |
| Preparing failover and failover | After data has been seeded or database files reattached, system prepares for failover. When everything is ready, the system performs a failover within a short downtime, usually under 10 seconds. |
| Old SQL instance cleanup |
Removes the old SQL process from the source virtual cluster. |
| Virtual cluster deletion / defragmentation | If it's the last managed instance within the source subnet, the final step deletes the virtual cluster synchronously. Otherwise, asynchronous defragmentation of the virtual cluster will be triggered. |
A detailed explanation of the operation steps can be found in the overview of Azure SQL Managed Instance management operations.
Readiness of the destination subnet
To deploy a managed instance or move it to another subnet, the destination subnet must meet certain network requirements. Based on the degree at which these requirements are met, we classify subnets into three states. Depending on the subnet state and designation, automatic or manual adjustments may be required to fully prepare it to act as a destination.
- Ready for managed instance (contains existing SQL Managed Instance)
- Description: This subnet already contains a SQL Managed Instance and is ready to serve as a destination. When selecting the destination subnet in Azure portal, an icon will show next to this subnet's virtual network as a visual aid.
- Adjustment: No adjustments are necessary.
- Ready for managed instance (empty)
- Description: An empty subnet that can be automatically adjusted to serve as a destination.
- Adjustment: The workflow validates all the required rules in the network security group and adds any rules that are missing. Depending on the instance configuration (public endpoint, connection type for private endpoint), additional rules may be deployed.
- Other
- Description: A subnet marked as Other is empty and can be used for new Managed Instance deployments, but not as a destination when moving an existing one. This subnet requires your attention in order to appear as Ready.
- Your action required: The subnet needs to be adjusted according to the network requirements. You have two options: either use a pre-defined automation script for configuring an existing virtual network for Azure SQL Managed Instance or prepare the subnet manually by delegating it to SQL Managed Instance and assigning a route table and a network security group.
- Invalid
- Description: Subnets marked as Invalid cannot be used for new or existing managed instances, either because they're already in use (instances used for instance deployments cannot contain other resources), or the subnet has a different DNS zone (a cross-subnet instance move limitation).
Limitations on the destination subnet
When choosing a destination subnet for an existing instance, consider the following limitations:
- Destination subnet must be in the same virtual network as the source subnet.
- DNS zone of the destination subnet must match the DNS zone of the source subnet. Changing the DNS zone of a managed instance is not supported.
- If you want to upgrade Gen 4 to Gen 5 and you don’t have sufficient IP addresses in the current subnet, or you want to move upgraded instance to a new subnet, merge these operations as part of the same request.
How to move the instance
A cross-subnet instance move is part of the instance update operation. Existing instance update API, Azure PowerShell, and Azure CLI commands 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
$currentSubscriptionID = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
$sqlMIResourceGroupName = 'contoso-rg'
$sqlMIResourceVnetName = 'vnet-contoso'
$destinationSubnetName = 'contoso-destination-subnet'
$sqlMIName = 'contoso-mi'
### 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 = $currentSubscriptionID
resourceGroupName = $sqlMIResourceGroupName
virtualNetworkName = $sqlMIResourceVnetName
subnetName = $destinationSubnetName
}
#Initiating subnet preparation script
Invoke-Command -ScriptBlock ([Scriptblock]::Create((iwr ($scriptUrlBase+'/delegateSubnet.ps1?t='+ [DateTime]::Now.Ticks)).Content)) -ArgumentList $parameters
###
### PART 3 – Move instance to the new subnet
#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/$currentSubscriptionID/resourceGroups/$sqlMIResourceGroupName/providers/Microsoft.Network/virtualNetworks/$sqlMIResourceVnetName/subnets/$destinationSubnetName" -AsJob -Force
###
### PART 3 (OPTIONAL) – Monitor operation progress
#loading the operation progress (uncomment to call)
#$operationProgress = Get-AzSqlInstanceOperation -ManagedInstanceName $sqlMIName -ResourceGroupName $sqlMIResourceGroupName
#checking the operation step status (uncomment to call)
#Write-Host "Checking the ongoing step" -ForegroundColor Yellow
#$operationProgress.OperationSteps.StepsList
Summary
In this article, we introduced a feature for moving the instances across the subnets that makes it much easier to move the instances across different network environments or overcome inability to execute management operations due to lack of IP addresses. More importantly, instances can now be moved across the subnets with a minimal downtime.
Stay tuned with #sqlmiops!
Index article: Azure SQL Managed Instance – introduction to #SQLMIOPS
Call to action: Nominate the topics for upcoming blog posts using comment section and votes, or writing directly to mdcssqlmipm@microsoft.com
