Azure SQL Managed Instance pools: new features

[Edit: Nov 2024] Announced General Availability of instance pools.

 

We are happy to announce new features that round out instance pools feature set to provide the full functionality required for a wide range of use cases.

 

What are instance pools?

When migrating small SQL Server instances to Azure it is often the case that a single SQL Managed Instance turns out to be overkill in terms of size and, consequently, cost. The oversizing problem can happen whenever very small instances are required, for example when an ISV company builds a multi-tenant app requiring a small SQL MI instance for each customer. In such cases the smallest size (4-vCores) for a single SQL MI can still turn out to be too large and too expensive for the given use case. This is where SQL MI pools ("instance pools") deliver great value.

 

Instance pools provide the unique capability to provision small, cost-effective 2-vCores instances within a pre-provisioned instance pool. This way, small instances can cost 50% less compared to non-pooled instances. As an additional advantage, pooled instances provision very fast (under 5 minutes) which recommends them for many scenarios that involve rapid capacity scaling.

 

 

What is new?

Today we are announcing the following new features:

• Instance pool can be updated (via PowerShell and Azure CLI) after its creation: changing compute size, license type, hardware type, maintenance window.
• Instance pool can be created via Azure Portal* (PowerShell, Azure CLI have already been supported, *Azure Portal support: from 2nd half of February)
• A single instance (SQL MI) can be created directly into an instance pool via Azure Portal* (PowerShell, Azure CLI have already been supported, *Azure Portal support: from 2nd half of February)
• A single instance (SQL MI) can be moved in and out of an instance pool via PowerShell and Azure CLI.
• Support for Premium Hardware (Gen8) has been added

The following table provides an overview of instance pool core features. A detailed description of features and limitations is described in What is an Azure SQL Managed Instance pool (preview).

 

 

Using new features

Here are some simple use examples for the latest features. When performing operations on instance pools or pooled instances it is recommended that one establishes a prior understanding of:

• Property scope: pool-level vs instance-level
• Unsupported features
• Resource limits
• Other restrictions

You can also refer to SQL Managed Instance pools how-to guide for more code examples.

 

Create instance pool (via Azure Portal*)

*Available in 2nd half of February

 

Instance pools have now a new create experience in Azure Portal:

 

Create SQL MI into an instance pool (via Azure Portal*)

*Available in 2nd half of February

 

Within Create Azure SQL Managed Instance experience in Azure Portal, it is now possible to select a pool to which the instance will belong:

 

 

 

Instance pool update (via PowerShell)

The following PowerShell code provides examples of updating an instance pool. Please note that license type, hardware type and fixed maintenance window option apply to all instances within the pool, as these properties have instance pool scope and cannot be set separately on a per-instance basis.

 

 

 

 

# obtain instance pool object $instancePool = Get-AzSqlInstancePool -ResourceGroupName $resourceGroupName -Name $instancePoolName # change license type $instancePool | Set-AzSqlInstancePool -LicenseType 'BasePrice' # change vCore size $instancePool | Set-AzSqlInstancePool -VCores 16 # change hardware type $instancePool | Set-AzSqlInstancePool -ComputeGeneration 'Gen8'

 

 

 

 

Move SQL MI into an instance pool (via PowerShell)

When moving an instance into an instance pool, the following points must be observed and managed by the user:

• Instance pool scoped properties: Service tier, Hardware type, License type and Maintenance Window are determined by the instance pool. If an instance entering the pool has a different property value than the pool, that property value has to be changed so it matches the pool’s property value. (For example, when a single instance with Hardware type = Gen5 and License type = BasePrice is being moved into an instance pool whose Hardware type = Gen8 and License type = LicenseIncluded, the instance has to be updated to the same pool’s properties values: Gen8, LicenseIncluded)
• Unsupported features: some features (e.g. failover groups) are not supported within instance pools.
• Resource limits: an instance will not be able to enter an instance pool in case there is e.g. not enough cores available in the pool or if other specified limits are not respected.
• Other restrictions:  the instance entering an instance pool has to be in the same subnet and resource group.

To move an instance into an instance pool one should update the instance by using Set-AzSqlInstance command while specifying the InstancePoolName parameter. In case other parameters need to be changed so they match pool’s parameters, this can be achieved within the same command.

 

 

 

 

# move instance into an instance pool $instance01 | Set-AzSqlInstance -InstancePoolName $instancePoolName

 

 

 

 

Move SQL MI out of an instance pool (via PowerShell)

When moving an instance out of an instance pool, the instance will retain all its properties except in two special cases:

• Moving a 2-vCores instance out of an instance: this is not possible as 2-vCore option is not available for non-pooled instances. In this case, a new value for vCore size has to be specified for the instance.
• Moving an instance from a pool which has one of license benefits activated (e.g. AHB – Azure Hybrid Benefit). This means that LicenseType is set to BasePrice for all pooled instances, so when the user moves an instance out of the pool, they either have to confirm that the non-pooled instance qualifies for a license benefit outside the pool and therefore have LicenseType set to BasePrice or they should change LicenseType to LicenseIncluded.

In PowerShell this is achieved using Set-AzSqlInstance command and providing empty value for -InstancePoolName parameter. The same command is used if other instance parameters need to be changed within the same update event.

 

 

 

 

# move instance out of an instance pool $instance01 | Set-AzSqlInstance -InstancePoolName “”

 

 

 

Conclusion

Instance pools (currently in Public Preview) provide a great way to cost-optimize you SQL Server workloads in Azure that require small SQL server instances. The complete feature set now allows many use scenarios that can take advantage of having small 2-vCore instances within instance pools, such as migrating small SQL Server instances from on-premises or Virtual Machines, using small cost-effective instances for Dev/Test environments, or leveraging small SQL MIs within multitenant applications.