Track progress of SQL Managed Instance create/scale request

by Scott Muniz | Sep 22, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Azure SQL Managed Instance provides management operations that you can use to automatically deploy new managed instances, update instance properties, and delete instances when no longer needed. All management operations can be categorized as follows:

• Instance deployment (new instance creation)
• Instance update (changing instance properties, such as vCores or reserved storage)
• Instance deletion

As result of connectivity and deployment architecture, instance deployment and scaling are long running operations. These operation can be monitored on couple of ways, but till now neither one of them was displaying full details on operation steps and progress.

Managed Instance Operations API introduced

With instance operations API in place you can monitor progress of create and scaling requests across tools including Azure Portal, PowerShell, Azure CLI or using REST API itself. API and tools have in place commands for retrieving operation details and canceling ongoing operations.

Get operation details

• Azure Portal
• PowerShell
• Azure CLI
• REST API

Command for retrieving operation details gives insight into:

• Operation start time
• Operation parameters – set of properties that retrieves a list of current and requested parameters for scaling operation. In case of create operation only requested parameters are returned
• Operation status – parameter that shows if operation is in progress, completed or has failed
• Is operation cancelable – deployment steps in operations API are high level logical steps. Some of the micro steps beneath cannot be abandoned. This parameter represents if operation can be canceled or not in current point of time
• Operation steps – set of properties that retrieves information on current step, total number of steps and each individual step details

Example of the API call for getting list of operations for the specific managed instance:

GET https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/my-rg/providers/Microsoft.Sql/managedInstances/my-managed-instance/operations?api-version=2019-06-01-preview

Example of the API response with list of operations:

[
   {
      "properties":{
         "managedInstanceName":"my-managed-instance",
         "operation":"UpsertManagedServer",
         "operationFriendlyName":"UPDATE MANAGED SERVER",
         "percentComplete":100,
         "startTime":"2019-12-06T11:08:44.49Z",
         "state":"Cancelled",
         "isCancellable":false
      },
      "id":"/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/my-rg/providers/Microsoft.Sql/managedInstances/my-managed-instance/operations/11111111-2222-2222-2222-111111111111",
      "name":"11111111-2222-2222-2222-111111111111",
      "type":"Microsoft.Sql/managedInstances/operations"
   },
   {
      "properties":{
         "managedInstanceName":"my-managed-instance",
         "operation":"UpsertManagedServer",
         "operationFriendlyName":"UPDATE MANAGED SERVER",
         "percentComplete":0,
         "startTime":"2019-12-06T11:08:44.49Z",
         "state":"InProgress",
         "isCancellable":true,
         "operationSteps":{
            "totalSteps":"6",
            "currentStep":2,
            "stepsList":[
               {
                  "order":1,
                  "name":"Request validation",
                  "status":"Completed"
               },
               {
                  "order":2,
                  "name":"Virtual Cluster resize/creation",
                  "status":"InProgress"
               },
               {
                  "order":3,
                  "name":"New SQL Instance Startup",
                  "status":"NotStarted"
               },
               {
                  "order":4,
                  "name":"Seeding database files",
                  "status":"NotStarted"
               },
               {
                  "order":5,
                  "name":"Preparing Failover and Failover",
                  "status":"NotStarted"
               },
               {
                  "order":6,
                  "name":"Old SQL Instance cleanup",
                  "status":"NotStarted"
               }
            ]
         }
      },
      "id":"/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/my-rg/providers/Microsoft.Sql/managedInstances/my-managed-instance/operations/11111111-1111-1111-1111-111111111111",
      "name":"11111111-1111-1111-1111-111111111111",
      "type":"Microsoft.Sql/managedInstances/operations"
   }
]

Operation is visible only 24 hours in API response. For full explanation of the API visit Monitoring Azure SQL Managed Instance management operations.

Cancel operation

• Azure Portal
• PowerShell
• Azure CLI
• REST API

Cancel operation is executed for specific operation performed on the managed instance. Operation names that are unique and that can be found as part of the GET operation details response, are used for this purpose.

Example of the API call for canceling operation:

POST https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/my-rg/providers/Microsoft.Sql/managedInstances/my-managed-instance/operations/11111111-1111-1111-1111-111111111111/cancel?api-version=2019-06-01-preview

Managed Instance Operations API use cases and examples

Get Operation

Command for returning operation with operation steps enables you to take dependent actions based on operation progress or simply track progress of the submitted operation.

Note: Examples displayed in this article are just the basics and there is a space for improvement in terms of additional validations or parametrization. Main goal of these examples is to bring closer  benefits of management operations API.

Example 1: deploy resources dependent on managed instance deployment

In create operation or general purpose vCores scaling, step with virtual cluster resize/creation is the longest one. After it is completed you could start spinning up some other environment or app that will be connected to the managed instance as remaining steps are the shorter one and create/update operation is close to finish. In both scenarios (create or GP vCores update) virtual cluster resize/creation step is second one. For full list of steps and their order of execution visit Management Operations overview documentation article. PowerShell example for the scenario could look like the following:

#Define parameters
$managedInstance = "managed-instance-name"
$resourceGroup = "resource-group-name"
$location = "westcentralus"
$subnetId = "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/resource-group-name/providers/Microsoft.Network/virtualNetworks/vnet-name/subnets/subnet-name"
$licenseType = "LicenseIncluded"
$vCores = 8
$storageSizeInGB = 256
$edition = "GeneralPurpose"
$hardwareGen = "Gen5"

#New SQL Managed Instance. Perform it As Job so script could proceed further.
$myNewMI = New-AzSqlInstance -Name $managedInstance -ResourceGroupName $resourceGroup -Location $location -AdministratorCredential (Get-Credential) -SubnetId $subnetId -LicenseType $licenseType -StorageSizeInGB $storageSizeInGB -VCore $vCores -Edition $edition -ComputeGeneration $hardwareGen -AsJob

#Wait for 5 minutes for validation to complete
Start-Sleep -s 300

#Get list of ongoing management operations for the instance
$managementOperations = Get-AzSqlInstanceOperation -ManagedInstanceName $managedInstance  -ResourceGroupName $resourceGroup

#Iterate over management operations to find ongoing one
foreach ($mo in $managementOperations ) {
	if($mo.State -eq "InProgress"){
		#Create ongoing operation object
        $ongoingOperation = $mo
		break
	}
}

#If there is ongoing operation
if ($ongoingOperation) {
    $operationName = $ongoingOperation.Name
    $operationSteps = $ongoingOperation.operationSteps
    $operationStep = $operationSteps.currentStep

    #While operation is in progress and operation step is less than 3 (as step 2 is virtual cluster resize/creation) do not proceed further.
    #Check state each 10 minutes (600 seconds)
    
    Write-Host "Operation status is: " $ongoingOperation.State
    Write-Host "Operation step is: " $operationStep
    
    while($ongoingOperation.State -eq "InProgress" -and $operationStep -lt 3) {
        Write-Host "Operation status is: " $ongoingOperation.State
        Start-Sleep -s 600
        $ongoingOperation = Get-AzSqlInstanceOperation -ManagedInstanceName $managedInstance  -ResourceGroupName $resourceGroup -Name $operationName
        $operationSteps = $ongoingOperation.operationSteps
        $operationStep = $operationSteps.currentStep
    }
    
    Write-Host "Operation result is: " $ongoingOperation.State
    Write-Host "Operation step is: " $operationStep

    #Here goes the code for starting dependent deployment
}

Example 2: deploy managed instances as part of the same DNS zone

Another scenario could be deploying two managed instances that should be part of the failover group. First instance deployment is started and operation status is checked. In addition to operation status, we can check if DNS zone field is defined for the managed instance. As soon as it is defined, we could start another instance deployment as part of the same DNS zone. Example:

#Define parameters
$managedInstance = "managed-instance-name"
$resourceGroup = "resource-group-name"
$location = "westcentralus"
$subnetId = "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/resource-group-name/providers/Microsoft.Network/virtualNetworks/vnet-name/subnets/subnet-name"
$licenseType = "LicenseIncluded"
$vCores = 8
$storageSizeInGB = 256
$edition = "GeneralPurpose"
$hardwareGen = "Gen5"

#New SQL Managed Instance. Perform it As Job so script could proceed further.
$myNewMI = New-AzSqlInstance -Name $managedInstance -ResourceGroupName $resourceGroup -Location $location -AdministratorCredential (Get-Credential) -SubnetId $subnetId -LicenseType $licenseType -StorageSizeInGB $storageSizeInGB -VCore $vCores -Edition $edition -ComputeGeneration $hardwareGen -Force -AsJob

#Wait for 5 minutes for validation to complete
Start-Sleep -s 300

#Get list of ongoing management operations for the instance
$managementOperations = Get-AzSqlInstanceOperation -ManagedInstanceName $managedInstance  -ResourceGroupName $resourceGroup

#Get SQL Managed Instance object and DNS zone property
$newInstance = Get-AzSqlInstance -Name $managedInstance -ResourceGroupName $resourceGroup

#Iterate over management operations to find ongoing one
foreach ($mo in $managementOperations ) {
	if($mo.State -eq "InProgress"){
		#Create ongoing operation object
		$ongoingOperation = $mo
		break
	}
}

#If there is ongoing operation
if ($ongoingOperation) {
    $operationName = $ongoingOperation.Name
    #While operation is in progress and DNS zone is not configured do not proceed further.
    #Check state each 10 minutes (600 seconds)
    
    Write-Host "Operation status is: " $ongoingOperation.State
    
    while($ongoingOperation.State -eq "InProgress" -and $null -eq $newInstance.DnsZone) {
        Write-Host "Operation status is: " $ongoingOperation.State
        Start-Sleep -s 600
        $ongoingOperation = Get-AzSqlInstanceOperation -ManagedInstanceName $managedInstance  -ResourceGroupName $resourceGroup -Name $operationName
        $newInstance = Get-AzSqlInstance -Name $managedInstance -ResourceGroupName $resourceGroup
    }
    
    Write-Host "Operation result is: " $ongoingOperation.State
    Write-Host "DNS Zone is: " $newInstance.DnsZone

    #Here goes the code for starting second instance deployment

    #Define parameters
    $managedInstanceFOG = "managed-instance-name-fog"
    $resourceGroupFOG = "resource-group-name-fog"
    $locationFOG = "westcentralus-fog"
    $subnetIdFOG = "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/resource-group-name-fog/providers/Microsoft.Network/virtualNetworks/vnet-name-fog/subnets/subnet-name-fog"
    $licenseTypeFOG = "LicenseIncluded"

    $myNewMIForFOG = New-AzSqlInstance -Name $managedInstanceFOG -ResourceGroupName $resourceGroupFOG -Location $locationFOG -AdministratorCredential (Get-Credential) -SubnetId $subnetIdFOG -LicenseType $licenseTypeFOG -StorageSizeInGB $storageSizeInGB -VCore $vCores -Edition $edition -ComputeGeneration $hardwareGen -Force -AsJob
}

Example 3: Scale up managed instance and kick off any data processing job that requires more compute

Customers are often facing with a situation where there are periodic jobs or tasks that require higher compute power which requires managed instance vCores scaling. Flow starts with instance scale up, then performing ETL or ML job (or any other) and then doing the instance scale down. Example script for this scenario:

#Define parameters
$managedInstance = "managed-instance-name"
$resourceGroup = "resource-group-name"
$sourceVcores = 8
$destVcores = 16

#Get SQL Managed Instance
$initialInstance = Get-AzSqlInstance -Name $managedInstance -ResourceGroupName $resourceGroup
Write-Host "Instance vCores value is: " $initialInstance.VCores

#Update SQL Managed Instance to 16 vCores. Force to skip verification. Perform it As Job so script could proceed further.
Set-AzSqlInstance -Name $managedInstance -ResourceGroupName $resourceGroup -VCore $destVcores -Force -AsJob

#Get list of ongoing management operations for the instance
$managementOperations = Get-AzSqlInstanceOperation -ManagedInstanceName $managedInstance  -ResourceGroupName $resourceGroup

#Iterate over management operations to find ongoing one
foreach ($mo in $managementOperations ) {
	if($mo.State -eq "InProgress"){
		#Create ongoing operation object
		$ongoingOperation = $mo
		break
	}
}

#If there is ongoing operation
if ($ongoingOperation) {
    $operationName = $ongoingOperation.Name
    
    #While operation is in progress do not proceed further. Check state each 10 minutes (600 seconds)
    Write-Host "Operation status is: " $ongoingOperation.State
    while($ongoingOperation.State -eq "InProgress") {
        Write-Host "Operation status is: " $ongoingOperation.State
        Start-Sleep -s 600
        $ongoingOperation = Get-AzSqlInstanceOperation -ManagedInstanceName $managedInstance  -ResourceGroupName $resourceGroup -Name $operationName
    }
    Write-Host "Operation result is: " $ongoingOperation.State

    #Get SQL Managed Instance and check if vCores are increased. If vCores are increased, start ML Process
    $scaledInstance = Get-AzSqlInstance -Name $managedInstance -ResourceGroupName $resourceGroup
    Write-Host "Instance vCores value is: " $scaledInstance.VCores
    if($scaledInstance.VCores -eq $destVcores ) {
        #Here goes the code for starting ML process which will be triggered after scaling operation is completed
        
        #Add code

        #After ML process is completed, scale down MI to 8 vCores. If there is need, adjust this part so it depends on ML result
        $restoredInstance = Set-AzSqlInstance -Name $managedInstance -ResourceGroupName $resourceGroup -VCore $sourceVcores
    }
}

Cancel operation

Cancellation of ongoing operation can be a handful when create or update request is submitted with wrong parameters or when create/update operation is running for longer than expected.

Example 1: Cancel create/update request without any condition

$managedInstance = "yourInstanceName"
$resourceGroup = "yourResourceGroupName"

$managementOperations = Get-AzSqlInstanceOperation -ManagedInstanceName $managedInstance  -ResourceGroupName $resourceGroup

foreach ($mo in $managementOperations ) {
	if($mo.State -eq "InProgress" -and $mo.IsCancellable){
		$cancelRequest = Stop-AzSqlInstanceOperation -ResourceGroupName $resourceGroup -ManagedInstanceName $managedInstance -Name $mo.Name
		Get-AzSqlInstanceOperation -ManagedInstanceName $managedInstance  -ResourceGroupName $resourceGroup -Name $mo.Name
	}
}

Example 2: Cancel operation that is running for more than 10 hours and create a support ticket

$managedInstance = "yourInstanceName"
$resourceGroup = "yourResourceGroupName"

$managementOperations = Get-AzSqlInstanceOperation -ManagedInstanceName $managedInstance  -ResourceGroupName $resourceGroup

#Iterate over management operations to find ongoing one
foreach ($mo in $managementOperations ) {
	if($mo.State -eq "InProgress"){
		#Create ongoing operation object		
		$ongoingOperation = $mo
		break
	}
}

#If there is ongoing operation
if ($ongoingOperation) {
	$operationName = $ongoingOperation.Name
	$currentDateTime = Get-Date
	$startDateTime = $ongoingOperation.StartTime
	$timeDiff = New-TimeSpan –Start $startDateTime –End $currentDateTime

	#While operation is in progress and lasts less then 10 hours do not proceed further. Check state each 10 minutes (600 seconds)
	Write-Host "Operation status is: " $ongoingOperation.State
	
	while($ongoingOperation.State -eq "InProgress" -and $timeDiff.Hours -le 10) {
		Write-Host "Operation status is: " $ongoingOperation.State

		Start-Sleep -s 600
		$ongoingOperation = Get-AzSqlInstanceOperation -ManagedInstanceName $managedInstance  -ResourceGroupName $resourceGroup -Name $operationName
		$currentDateTime = Get-Date
		$timeDiff = New-TimeSpan –Start $startDateTime –End $currentDateTime
	}
	Write-Host "Operation result is: " $ongoingOperation.State
	$ongoingOperation = Get-AzSqlInstanceOperation -ManagedInstanceName $managedInstance  -ResourceGroupName $resourceGroup -Name $operationName

	#If operation is still in progress, and we are out of the loop (which means operation is running for more then 10 hours) create a support ticket
	if($ongoingOperation.State -eq "InProgress"){
		$managedInstance = Get-AzSqlInstance -Name $managedInstance -ResourceGroupName $resourceGroup		
		
		# Service GUID 9b629e89-4ea0-53ec-9409-1579b8c41453 = SQL Database Managed Instance - For list of GUIDs check Get-AzSupportService
		# Problem Classification GUID ac342689-043e-d79b-6665-7edda4ecc61c = Service Tiers or Scaling Resources / Scaling an instance (compute, storage, and service tier changes) - For list of GUIDs check Get-AzSupportProblemClassification
		$problemClassificationId = "/providers/Microsoft.Support/services/9b629e89-4ea0-53ec-9409-1579b8c41453/problemClassifications/ac342689-043e-d79b-6665-7edda4ecc61c"
		$supportTicket = New-AzSupportTicket -Name "test1" -Title "Test" -Description "Test" -Severity "minimal" -ProblemClassificationId $problemClassificationId -TechnicalTicketResourceId $managedInstance.Id -CustomerContactDetail @{FirstName = "first" ; LastName = "last" ; PreferredTimeZone = "pacific standard time" ; PreferredSupportLanguage = "en-us" ; Country = "USA" ; PreferredContactMethod = "Email" ; PrimaryEmailAddress = "user@contoso.com"}
	}
}