Edit

Create an instance pool (preview) - Azure SQL Managed Instance

  • Article

Applies to: Azure SQL Managed Instance

This article teaches how to create an instance pool for Azure SQL Managed Instance, as well as how to move instances in and out of the pool.

Instance pools make it possible to deploy multiple instances with shared resources to a single virtual machine, which provides a convenient and cost-effective infrastructure to migrate multiple SQL Server instances without having to consolidate smaller and less compute-intensive workloads onto a larger SQL Managed Instance.

Note

Instance pools for Azure SQL Managed Instance are currently in preview.

Prerequisites

To create an instance pool, you should have:

Subnet size considerations

Carefully plan the size of your subnet if you plan to use an instance pool. Refer to Determine required subnet size & range for subnet sizing guidelines.

Use the following formula when calculating the number of IP addresses required by one instance pool containing multiple General Purpose instances:

2 * (5 + (3 * # of MIs)) + 5

The # of MIs refers to the maximum potential number of instances you plan to provision. The maximum possible number of instances in the pool is 40.

Create instance pool

You can create an instance pool using PowerShell or the Azure CLI. Consider the following:

  • Only the General Purpose service tier on either standard-series (Gen5) or premium-series hardware is currently available.
  • The pool name can contain only lowercase letters, numbers and hyphens, and can't start with a hyphen.
  • The Azure Hybrid Benefit is applied at the instance pool level. You can set the license type when you create the pool, and update the license type after the pool is created.

Important

Deploying an instance pool is a long running operation that can take up to 4.5 hours.

To create your instance pool, use New-AzSqlInstancePool.

Consider the following:

  • For LicenseType, use BasePrice for the Azure Hybrid Benefit or LicenseIncluded if you have don't have a SQL Server license that can be used for the Azure Hybrid Benefit discount.
  • Use Get-AzLocation | select displayname, location to obtain a list of regions where instance pools are available.

Create a new instance pool with 8 vCores on standard-series (Gen5) hardware by running the following sample script:

# Identify the SubnetId
$virtualNetwork = Get-AzVirtualNetwork -Name <vnet name> -ResourceGroupName <resource group name>
$miSubnet = Get-AzVirtualNetworkSubnetConfig -Name <subnet name> -VirtualNetwork $virtualNetwork
$miSubnetConfigId = $miSubnet.Id

# Create the instance
$instancePool = New-AzSqlInstancePool `
    -ResourceGroupName  <resource group name> `
    -Name <instance pool name> `
    -SubnetId $miSubnetConfigId `
    -LicenseType LicenseIncluded `
    -VCore 8 `
    -Edition GeneralPurpose `
    -ComputeGeneration Gen5 `
    -Location <region>

Create new instance inside pool

After your pool is created, you can create a new instance within the pool by using PowerShell, or the Azure CLI.

To identify pool parameters, use Get-AzSqlInstancePool then create your instance inside the specific pool with New-AzSqlInstance.

Create a new instance in your pool by running the following sample script:

$adminCredential = Get-Credential
$instancePool = Get-AzSqlInstancePool -ResourceGroupName <resource group name> -Name <instance pool name>

$instance01 = $instancePool | New-AzSqlInstance `
    -Name $instance01`
    -VCore 2 `
    -StorageSizeInGB 32 `
    -AdministratorCredential $adminCredential `

Move existing instance

You can move an existing instance into and out of a pool by using PowerShell or the Azure CLI if:

  • It's in the same resource group as the pool.
  • It's on the same virtual network and subnet as the pool.
  • It fits the instance pool resource limits.

When an existing instance is moved into a pool, settings at the pool level take precedence over instance-level settings. For example, the instance inherits the license type and maintenance window set at the pool level. When an instance is moved out of the pool, it retains the settings it inherited from the pool. The only exception is with the license type, which defaults back to 'LicenseIncluded' when an instance is removed from the instance pool - the Azure Hybrid Benefit and hybrid failover rights benefit must be configured manually after an instance is moved out of a pool.

To move an instance into a pool, provide the pool name when you use Set-AzSqlInstance:

$instance01 | Set-AzSqlInstance -InstancePoolName $instancePoolName

To move an instance out of a pool, provide a blank pool name:

$instance01 | Set-AzSqlInstance -InstancePoolName ''

Connect to instance in a pool

To connect to an instance in a pool, first enable the public endpoint for the instance, and then allow public endpoint traffic on the network security group.

To enable the public endpoint for an instance, set -PublicDataEndpointEnabled to true when you update instance properties with Set-AzSqlInstance:

$instance01 | Set-AzSqlInstance -InstancePoolName $instancePoolName -PublicDataEndpointEnabled $true

Create a database

Creating a database for an instance inside a pool is the same as creating a database for a single instance. You can create a new database by using PowerShell or the Azure CLI.

To create a new database for your instance, use New-AzSqlInstanceDatabase:

New-AzSqlInstanceDatabase -Name <database name> -InstanceName <instance name> -ResourceGroupName <resource group>

Get pool usage

You can use PowerShell to determine how resources are being used inside a pool.

To get a list of instances inside a pool, use Get-AzSqlInstance:

$instancePool | Get-AzSqlInstance

To get pool resource usage, use Get-AzSqlInstancePoolUsage:

$instancePool| Get-AzSqlInstancePoolUsage

You can add the -ExpandChildren parameter to get a detailed overview of the pool and instances inside it:

$instancePool | Get-AzSqlInstancePoolUsage –ExpandChildren

To list the databases in an instance, use Get-AzSqlInstanceDatabase:

$databases = Get-AzSqlInstanceDatabase -InstanceName $instance01Name  -ResourceGroupName $resourceGroupName

Note

To check limits on the instances deployed to a pool, and databases per instance pool, review resource limits.

Update an instance pool

You can use PowerShell to make changes to the instance pool limits.

The following sample script changes the license type, vCore size, and hardware type:

Change license type:

$instancePool | Set-AzSqlInstancePool -LicenseType BasePrice -VCores 16 -ComputeGeneration Gen8

You can also determine the available maintenance window schedules:

# 'Available maintenance schedules in $location'
$configurations = Get-AzMaintenancePublicConfiguration
$configurations | ?{ $_.Location -eq $location -and $_.MaintenanceScope -eq "SQLManagedInstance"} 
$maintenanceWindowOptions = $configurations | ?{ $_.Location -eq $location -and $_.MaintenanceScope -eq "SQLManagedInstance"}

You can then change the maintenance window by specifying a window option, such as:

$instancePool | Set-AzSqlInstancePool -MaintenanceConfigurationId $maintenanceWindowOptions[1].Id

Update a pooled instance

If pool resource limits haven't been exceeded, you can modify resource configurations for an instance inside a pool using PowerShell or the Azure CLI,

To modify resource parameters for an instance inside a pool, use Set-AzSqlInstance.

For example, the following script updates the vCores to 8 and changes the storage size to 512 GB for Instance1:

$instance1name | Set-AzSqlInstance -VCore 8 -StorageSizeInGB 512 -InstancePoolName $instancePoolName

Delete an instance pool

You can delete an instance pool by using PowerShell or the Azure CLI, once all instances in the pool have either been deleted, or moved out of the pool.

To delete an instance pool, use Remove-AzSqlInstancePool.

The following sample script deletes an empty instance pool:

Remove-AzSqlInstancePool -ResourceGroupName <resource group name -Name <instance pool name>

Instance pool operations

The following table shows available instance pool operations:

Command Azure portal PowerShell Azure CLI
Create an instance pool No Yes Yes
Update pool properties No Yes Yes
Check a pool use and properties Yes Yes Yes
Delete an instance pool Yes Yes Yes
Create a managed instance inside a pool No Yes Yes
Move a managed instance into a pool No Yes Yes
Delete a managed instance from a pool Yes Yes Yes
Move a managed instance out of a pool No Yes Yes
Create a database in instance within a pool Yes Yes Yes
Delete a database from SQL Managed Instance Yes Yes Yes

To use PowerShell, install the latest version of PowerShell Core, and follow instructions to Install the Azure PowerShell module.

Available PowerShell commands:

Cmdlet Description
New-AzSqlInstancePool Creates an instance pool.
Get-AzSqlInstancePool Returns information about an instance pool.
Set-AzSqlInstancePool Sets properties for an instance pool.
Remove-AzSqlInstancePool Removes an instance pool.
Get-AzSqlInstancePoolUsage Returns information about instance pool usage.

For operations related to instances both inside pools and single instances, use the standard managed instance commands, but the instance pool name property must be populated when using these commands for an instance in a pool.

Limitations

During public preview, instances in a pool have the following limitations:

  • The pool name can contain only lowercase letters, numbers and hyphens, and can't start with a hyphen.
  • All instances in the pool use the same licensing model. When you specify a license model for an instance that is different than the license model for the pool, the pool license model is used. When the instance is moved out of the pool, it automatically switches to a full paid license (LicenseType = 'LicenseIncluded'). Manually activate the Azure Hybrid Benefit or the hybrid failover rights benefit to change the licensing model.
  • Pooled instances must belong to the same subnet and resource group. Moving an instance in and out of the pool is only possible within the subnet of the pool and same resource group.
  • Only the General Purpose service tier is available on standard-series (Gen5) or premium-series hardware. The Next-gen General Purpose, Business Critical service tier, and premium-series memory optimized hardware isn't available.
  • The maximum possible number of instances in the pool is 40.
  • An instance pool can only be deleted after all instances in the pool are either deleted or moved out of the pool.
  • You can't use the Azure portal to:
    • Configure the instance pool. Use PowerShell or the Azure CLI instead.
    • Move instances in and out of the pool. Use PowerShell or the Azure CLI instead.
  • The following SQL Managed Instance features aren't supported when instances are in a pool:

Support requests

Create and manage support requests for instance pools in the Azure portal.

If you're experiencing issues when creating or deleting an instance, be sure to specify Instance Pools in the Problem subtype field.

Screenshot of the Instance pools support request in the Azure portal.

If you're experiencing issues related to a single managed instance or database within a pool, you should create a regular support ticket for Azure SQL Managed Instance.

To create larger SQL Managed Instance deployments (with or without instance pools), you might need to obtain a larger regional quota. For more information, see Request quota increases for Azure SQL Database. The deployment logic for instance pools compares total vCore consumption at the pool level against your quota to determine whether you're allowed to create new resources without further increasing your quota.

Related content