Edit

Share via

Duration of management operations in Azure SQL Managed Instance

Applies to: Azure SQL Managed Instance

This article details the steps and the duration of management operations in Azure SQL Managed Instance.

For an overview of the underlying processes related to management operations, such as seeding, and failover, see Management operations overview.

Management operation steps

Managing Azure SQL Managed Instance involves the following operations:

  • Create: The operations that occur when you create a new SQL managed instance. This includes creating or resizing the underlying virtual machine (VM) group, and deploying the SQL Database Engine process.
  • Update: Operations that occur when you change the properties of an existing SQL managed instance, such as scaling compute or storage, changing the service tier, or updating the instance configuration. Making updates often involves creating or resizing the underlying virtual machine (VM) group, as well as seeding data, and then failing over, to a new SQL Database Engine process.
  • Delete: Operations that occur when you delete an existing SQL managed instance, including the cleanup of resources such as the VM group associated with the instance.

Create operation

The Create operation initiates the deployment of a new SQL managed instance within a virtual network subnet, while setting up compute, storage, and the SQL Database Engine environment for the instance.

The creation process typically goes through three phases:

  1. Validate request: The submitted parameters are syntactically and semantically validated. If parameters are invalid (such as the wrong subnet, or unsupported SKU) the operation immediately fails with an error.
  2. Create or resize the VM group: Creates or extends a VM group to host the new instance. The duration of the operation depends on whether or not the instance is zone redundant or not.
  3. Start up new SQL instance: Deploys and starts the SQL Database Engine process on the allocated VMs.

Update operation

The Update operation modifies the properties of an existing SQL managed instance, such as scaling compute or storage, changing the service tier, or updating the instance configuration.

The update process typically goes through five phases:

  1. Validate request: The submitted parameters are syntactically and semantically validated. Checks for supported update types based on the current instance configuration and the requested changes. If the request is invalid, the operation fails with an error.
  2. Create or resize the VM group: Depending on the change, the existing VM group is resized or a new VM group is created, such as in the following update operations:
    • Scaling storage up or down
    • Scaling compute up or down
    • Changing the service tier
    • Changing the hardware
    • Adjusting the maintenance window
    • Enabling or disabling zone redundancy
  3. Start up SQL instance: A new SQL Database Engine process is initialized with the updated configuration.
    • If a new VM group is created, or if the existing VM group is resized, a full SQL Database Engine deployment occurs.
  4. Seed / attach storage: Prepares database on the new or resized VM group. The instance is available during this process.
  5. Prepare for, and then fail over: Traffic is redirected to the new instance.
    • Your instance is only unavailable during failover, when traffic is rerouted to the new SQL Database Engine process. In the Business Critical service tier, your instance is unavailable for up to 20 seconds, while in the General Purpose service tier, your instance can be unavailable for up to 2 minutes.
  6. Clean up old SQL instance: Deallocate the old virtual machines and delete the SQL processes that are no longer required.

Important

Scaling compute or storage, or changing the service tier, at the same time as long-running transactions (such as importing data, data processing jobs, or index rebuild) isn't recommended as the failover of the database at the end of the operation cancels all ongoing transactions.

Delete operation

The Delete operation removes an existing SQL managed instance and cleans up the associated resources. As soon as a delete operation is triggered, billing for SQL Managed Instance is disabled. The duration of the delete operation doesn't affect the billing.

The delete process typically goes through four phases:

  1. Validate request: The submitted parameters are syntactically and semantically validated. If the request is invalid, the operation fails with an error.
  2. Tail-log backup: If the instance is not empty, a tail-log backup is taken for every database to ensure that no data is lost after the instance is deleted. Backups are kept based on the retention policy of each database.
  3. SQL instance cleanup: The SQL Database Engine process is removed from the VM group, and the resources associated with the instance are deallocated.
  4. Delete VM group: If there are other instances in the subnet, the VM group remains intact for those instances. If the instance being deleted is the last instance in the subnet, the VM group is synchronously deleted as the last step. When the last instance in a subnet is deleted, removing the VM group automatically initiates the removal of the virtual cluster.

Instance pools

Instance pools allow you to create and manage multiple instances with shared resources, which can help reduce costs and simplify management. Deploying an individual instance within an existing pool is significantly faster than provisioning a standalone managed instance because the infrastructure is already available.

Create an instance pool involves the following steps:

  • Validate request: The submitted parameters are syntactically and semantically validated. If the request is invalid, the operation fails with an error.
  • Create the VM group: A new VM group is created to host the instance pool within a subnet of an Azure virtual network. The number of vCores allocated to the virtual cluster is the maximum total vCores used by all instances in the pool. This is a one-time operation that sets up the underlying infrastructure for multiple managed instances.
  • Create instance: Instances are created within the instance pool, which involves deploying the SQL Database Engine process on the allocated VMs. The instances share the resources of the virtual cluster, which allows for more efficient resource utilization. Instances are created by the customer as needed.

Create an instance inside a pool involves the following steps:

  • Validate request: The submitted parameters are syntactically and semantically validated. If the request is invalid, the operation fails with an error.
  • Create instance: Instances are created within the instance pool, which involves deploying the SQL Database Engine process on the allocated VMs.

Moving an instance to an instance pool involves the following steps:

  • Validate request: The submitted parameters are syntactically and semantically validated. If the request is invalid, the operation fails with an error.
  • Allocate vCores: The instance must be assigned adequate number of required vCores from the pool. As we already have vCores provisioned to the pool, this is easy and works the same as provisioning a new instance inside the pool.

Moving an instance out of an instance pool involves the following steps:

  • Validate request: The submitted parameters are syntactically and semantically validated. If the request is invalid, the operation fails with an error.
  • Create or resize the VM group: This requires providing an adequate number of required vCores to the instance outside of the pool. vCores aren't ready and must be provisioned, so this operation is the same as any update duration that must resize an existing VM group, or create a new VM group.

Zone Redundancy

With zone redundancy enabled, compute and storage layers are distributed across multiple availability zones to ensure high availability and data integrity.

Zone redundancy extends the duration of management operations to accommodate changes to resources across multiple availability zones.

Management operation duration

The duration of management operations varies based on the service tier of the SQL Managed Instance. The following sections provide detailed information about the duration of management operations for each service tier:

The following table details the duration of management operations in the General Purpose service tier, including the long-running segments and estimated duration for each operation:

Management operation Long-running segments Estimated duration
Create operations
Creating a new instance Creating or resizing VM group 95% of operations finish in 30 minutes
Creating a new zone-redundant instance Creating or resizing VM group with zone redundancy 95% of operations finish in 4 hours
Creating new instance pool Creating the VM group 95% of operations finish in 30 minutes
Creating instance inside a pool None 95% of operations finish in less than 10 minutes
Update operations
Changing basic instance properties such as the license type or Microsoft Entra None Up to 1 minute
Scaling storage None 99% of operations finish in 5 minutes
Scaling compute (vCores) Creating or resizing VM group 95% of operations finish in 60 minutes
Changing to Business Critical service tier Resizing the VM group
+ Database seeding		 95% of operations finish in 60 minutes + time to seed databases
Changing to Next-gen General Purpose service tier Creating or resizing VM group
+ Database seeding		 95% of operations finish in 60 minutes + time to seed databases
Changing hardware or maintenance window Creating or resizing VM group 95% of operations finish in 60 minutes
Enabling zone redundancy Creating new VM group
+ Database seeding		 95% of operations finish in 4 hours + time to seed databases
Disabling zone redundancy Creating new VM group
+ Database seeding		 95% of operations finish in 30 minutes + time to seed databases
Moving an instance to an instance pool None 95% of operations finish in 10 minutes
Moving an instance out of an instance pool Creating or resizing VM group 95% of operations finish in 60 minutes
Delete operations
Deleting non-last instance1 Log tail backup for all databases 90% of operations finish in 1 minute.
Deleting last instance2 Log tail backup for all databases
Deleting virtual cluster		 95% of operations finish in 90 minutes

1 If there are multiple VM groups in the cluster, deleting the last instance in the group immediately triggers deleting the VM group asynchronously.
2 Deleting the last instance in the subnet immediately triggers deleting the virtual cluster synchronously.

Your instance is available for the duration of all management operations, except during the final failover step, when traffic is redirected to the new SQL Database Engine process. In the Business Critical service tier, your instance is unavailable for up to 20 seconds, while in the General Purpose and Next-gen General Purpose service tiers, your instance can be unavailable for up to 2 minutes.

Seeding duration

Seeding is the process of initializing and synchronizing data across SQL Database Engine processes. The duration of seeding primarily depends on the size of the database. On average, seeding proceeds at a rate of approximately 220 GB per hour.

Seeding is executed concurrently through eight parallel channels. At any given time, eight databases are selected for data transfer. As soon as the transfer of one database completes, the next available database is assigned to the now free channel, which ensures continuous and efficient throughput.

The following table provides the following information:

  • Likely estimated seeding time for the majority of cases
  • Expected maximum estimated seeding time for 95% of cases
Database size range (GB) Likely seeding time Expected maximum seeding time
0 - 32 GB 30 minutes 1 hour
32 - 256 GB 1.5 hours 2 hours
256 - 512 GB 2 hours 5 hours
512 - 1024 GB 5 hours 9 hours
1024 - 2048 GB 9 hours 15 hours
2048 - 3072 GB 10 hours 16 hours
3072 - 4096 GB 12 hours 18 hours
Greater than 4096 GB 15 hours 20 hours

Related content