Редагувати

Поділитися через


Overview of Azure SQL Managed Instance management operations

Applies to: Azure SQL Managed Instance

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.

What are management operations?

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

To support deployments within Azure virtual networks and provide isolation and security for customers, SQL Managed Instance relies on virtual clusters. The virtual cluster represents a dedicated set of isolated virtual machines deployed inside the customer's virtual network subnet and organized in virtual machine groups. Essentially, every managed instance deployed to an empty subnet results in a new virtual cluster buildout which builds the very first virtual machine group.

Subsequent management operations on managed instances can impact the underlying virtual machine groups. Changes that impact the underlying virtual machine groups might affect the duration of management operations, as deploying additional virtual machines to the virtual cluster comes with an overhead that you need to consider when you plan new deployments or updates to existing managed instances.

Fast provisioning

Instances with certain configurations can benefit from fast SQL Managed Instance provisioning, which reduces the time it takes to create your first instance in a subnet to 30 minutes (down from an average of 45-60 minutes). To learn more about operation duration times, review management operations.

Fast provisioning only applies:

  • to the first instance provisioned in the subnet.
  • to instances with 4-8 vCores.
  • to instances that use the default maintenance window.
  • to instances that are not zone redundant.

Duration

The duration of operations on the virtual cluster can vary, but typically have the longest duration.

The following table lists the long running steps that can be triggered as part of the create, update, or delete operation. Table also lists the durations that you can typically expect, based on existing service telemetry data:

Step Description Estimated duration
Virtual cluster creation (fast provisioning)1 Fast provisioning is a synchronous step in instance management operations during which the very first virtual machine group is instantly available. 90% of operations finish in 30 minutes
Virtual cluster creation Creation is a synchronous step in instance management operations during which the very first virtual machine group is created. 90% of operations finish in less than 4 hours
Virtual cluster resizing (expansion or shrinking) Adding new machines to the existing virtual machine group, removing unused virtual machines, adding or removing the entire virtual machine group. Expansion is a synchronous step, while shrinking is performed asynchronously (without impact on the duration of instance management operations). 90% of cluster expansions with creation of new virtual machine group finish in less than 4 hours

90% of cluster expansions with expansion of existing virtual machine group finish in 60 minutes
Virtual cluster deletion Virtual cluster deletion is triggered when the very last instance is deleted from the subnet. 90% of cluster deletions finish in 1.5 hours
Seeding database files2 A synchronous step, triggered during compute (vCores), or storage scaling in the Business Critical service tier as well as in changing the service tier from General Purpose to Business Critical (or vice versa). Duration of this operation is proportional to the total database size as well as current database activity (number of active transactions). Database activity when updating an instance can introduce significant variance to the total duration. 90% of these operations execute at 220 GB/hour or higher

1 Fast provisioning is currently supported only for the first instance in the subnet, with 4 or 8 vCores, and with default maintenance window configuration.
2 When scaling compute (vCores) or storage in Business Critical service tier, or switching service tier from General Purpose to Business Critical, seeding also includes Always On availability group seeding.

Important

Scaling storage up or down in the General Purpose service tier consists of updating meta data and propagating response for submitted request. It is a fast operation that completes in up to 5 minutes, without a downtime and failover.

Management operations long running segments

The following tables summarize operations and typical overall durations, based on the category of the operation:

Category: Deployment

Operation Long-running segment Estimated duration
First instance in an empty subnet1 Virtual cluster creation (fast provisioning) 90% of operations finish in 30 minutes.
First instance in an empty subnet Virtual cluster creation 90% of operations finish in less than 4 hours.
First instance with a different hardware generation or maintenance window in a non-empty subnet (for example, the first Premium-series instance in a subnet with Standard-series instances) Adding new virtual machine group to the virtual cluster2 90% of operations finish in less than 4 hours.
Subsequent instance creation within the non-empty subnet (2nd, 3rd, etc. instance) Virtual cluster resizing 90% of operations finish in 60 minutes.

1 Fast provisioning is currently supported only for the first instance in the subnet, with 4 or 8 vCores, and with default maintenance window configuration. 2 A separate virtual machine group is created for each hardware generation and maintenance window configuration.

Category: Update

Operation Long-running segment Estimated duration
Instance property change
(admin password, Microsoft Entra login, Azure Hybrid Benefit flag)
N/A Up to 1 minute.
Instance storage scaling up/down
(General Purpose)
No long-running segment 99% of operations finish in 5 minutes.
Instance storage scaling up/down
(Business Critical)
- Virtual cluster resizing
- Always On availability group seeding
90% of operations finish in 60 minutes + time to seed all databases (220 GB/hour).
Instance storage scaling up/down
(Next-gen General Purpose)
- Virtual cluster creation / virtual machine group resizing
- Always On availability group seeding
90% of operations finish in less than 4 hours (virtual machine group creation) or 60 minutes (virtual machine group resizing) + time to seed all databases (220 GB/hour) + failover + cleaning up old instance
Instance compute (vCores) scaling up and down
(General Purpose)
- Virtual cluster resizing 90% of operations finish in 60 minutes.
Instance compute (vCores) scaling up and down
(Business Critical)
- Virtual cluster resizing
- Always On availability group seeding
90% of operations finish in 60 minutes + time to seed all databases (220 GB/hour).
Instance compute (vCores) scaling up and down
(Next-gen General Purpose)
Virtual cluster creation / virtual machine group resizing
- Always On availability group seeding
90% of operations finish in less than 4 hours (virtual machine group creation) or 60 minutes (virtual machine group resizing) + time to seed all databases (220 GB/hour) + failover + cleaning up old instance
Instance service tier change
(General Purpose to Business Critical and vice versa)
- Virtual cluster resizing
- Always On availability group seeding
90% of operations finish in 60 minutes + time to seed all databases (220 GB/hour).
Instance service tier change
(General Purpose or Business Critical to Next-gen General Purpose and vice versa)
Virtual cluster creation / virtual machine group resizing
- Always On availability group seeding
90% of operations finish in less than 4 hours (virtual machine group creation) or 60 minutes (virtual machine group resizing) + time to seed all databases (220 GB/hour) + failover + cleaning up old instance
Instance hardware or maintenance window change
(General Purpose)
- Virtual cluster resizing1 90% of operations finish in less than 4 hours (virtual machine group creation) or 60 minutes (virtual machine group resizing) .
Instance hardware or maintenance window change
(Business Critical)
- Virtual cluster resizing1
- Always On availability group seeding
90% of operations finish in less than 4 hours (virtual machine group creation) or 60 minutes (virtual machine group resizing) + time to seed all databases (220 GB/hour).
Instance hardware or maintenance window change
(Next-gen General Purpose)
- Virtual cluster creation / virtual machine group resizing
- Always On availability group seeding
90% of operations finish in less than 4 hours (virtual machine group creation) or 60 minutes (virtual machine group resizing) + time to seed all databases (220 GB/hour) + failover + cleaning up old instance

1 Managed instance must be placed in a virtual machine group with the same corresponding hardware and maintenance window. If there is no such group in the virtual cluster, a new one must be created first to accommodate the instance configuration.

Category: Delete

Operation Long-running segment Estimated duration
Non-last instance deletion Log tail backup for all databases 90% of operations finish in up to 1 minute.1
Last instance deletion - Log tail backup for all databases
- Virtual cluster deletion
90% of operations finish in up to 1.5 hours.2

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

Important

As soon as delete operation is triggered, billing for SQL Managed Instance is disabled. Duration of the delete operation will not impact the billing.

Instance availability

SQL Managed Instance is available during update operations, except a short downtime caused by the failover that happens at the end of the update. It typically lasts up to 10 seconds even in case of interrupted long-running transactions, thanks to accelerated database recovery.

Note

Scaling General Purpose managed instance storage will not cause a failover at the end of update.

SQL Managed Instance is not available to client applications during deployment and deletion operations.

Important

It's not recommended to scale compute or storage of Azure SQL Managed Instance or to change the service tier at the same time as long-running transactions (data import, data processing jobs, index rebuild, etc.). The failover of the database at the end of the operation cancels all ongoing transactions.

Management operations steps

Management operations consist of multiple steps. With Operations API introduced these steps are exposed for subset of operations (deployment and update). Deployment operation consists of three steps while update operation is performed in six steps. For details on operations duration, see the management operations duration section. Steps are listed by order of execution.

Managed instance deployment steps

Step name Step description
Request validation Submitted parameters are validated. In case of misconfiguration operation will fail with an error.
Virtual cluster resizing / creation Depending on the state of the virtual cluster, the cluster goes into creating or resizing state.
New SQL instance startup SQL process is started on the deployed virtual machines.

Managed instance update steps

Step name Step description
Request validation Submitted parameters are validated. In case of misconfiguration operation will fail with an error.
Virtual cluster resizing / creation Depending on the state of the virtual cluster, the cluster goes into creating or resizing state.
New SQL instance startup SQL process is started on the deployed virtual machines.
Seeding database files / attaching database files Depending on the type of the update operation, either database seeding or attaching database files is performed.
Preparing failover and failover After data has been seeded or database files reattached, system is being prepared for the failover. When everything is set, failover is performed with a short downtime.
Old SQL instance cleanup Removing old SQL process from the virtual machines.

Managed instance delete steps

Step name Step description
Request validation Submitted parameters are validated. In case of misconfiguration operation will fail with an error.
SQL instance cleanup Removing SQL process from the virtual machines.
Virtual cluster deletion Depending if the instance being deleted is last in the subnet, virtual cluster is synchronously deleted as last step.

Note

As a result of scaling instances, underlying virtual cluster will go through process of releasing unused capacity and possible capacity defragmentation, which could impact instances that did not participate in creation / scaling operations.

Management operations cross-impact

Management operations on a managed instance can affect the management operations of other instances placed inside the same subnet:

  • Long-running restore operations in a virtual cluster put other operations in the same virtual machine group on hold, such as creation or scaling operations.
    Example: If there is a long-running restore operation and also a scale request that requires shrinking the virtual machine group, the shrink request will take longer to complete as it waits for the restore operation to finish before it can continue.

  • A subsequent instance creation or scaling operation is put on hold by a previously initiated instance creation or instance scale that initiated a resize of the virtual machine group.
    Example: If there are multiple create and/or scale requests in the same subnet under the same virtual machine group, and one of them initiates a virtual machine group resize, all requests that were submitted 5+ minutes after the initial operation request will last longer than expected, as these requests will have to wait for the resize to complete before resuming.

  • Create/scale operations submitted in a 5-minute window will be batched and executed in parallel.
    Example: Only one virtual cluster resize will be performed for all operations submitted in a 5-minute window (measuring from the moment of executing the first operation request). If another request is submitted more than 5 minutes after the first one is submitted, it will wait for the virtual cluster resize to complete before execution starts.

Important

Management operations that are put on hold because of another operation that is in progress will automatically be resumed once conditions to proceed are met. No user action is necessary to resume the temporarily paused management operations.

Monitoring management operations

To learn how to monitor management operation progress and status, see Monitoring management operations.

Canceling management operations

To learn how to cancel management operation, see Canceling management operations.

Next steps