Azure SQL Database instance pools (preview) how-to guide

This article provides details on how to create and manage instance pools.

Instance pool operations

The following table shows the available operations related to instance pools and their availability in the Azure portal and PowerShell.

Command Azure portal PowerShell
Create instance pool No Yes
Update instance pool (limited number of properties) No Yes
Check instance pool usage and properties No Yes
Delete instance pool No Yes
Create managed instance inside instance pool No Yes
Update managed instance resource usage Yes Yes
Check managed instance usage and properties Yes Yes
Delete managed instance from the pool Yes Yes
Create a database in managed instance placed in the pool Yes Yes
Delete a database from managed instance Yes Yes

Available PowerShell commands

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

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

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.

How to deploy managed instances into pools

The process of deploying an instance into a pool consists of the following two steps:

  1. One-off instance pool deployment. This is a long running operation, where the duration is the same as deploying a single instance created in an empty subnet.

  2. Repetitive instance deployment in an instance pool. The instance pool parameter must be explicitly specified as part of this operation. This is a relatively fast operation that typically takes up to 5 minutes.

In public preview, both steps are only supported using PowerShell and Resource Manager templates. The Azure portal experience is not currently available.

After a managed instance is deployed to a pool, you can use the Azure portal to change its properties on the pricing tier page.

Create an instance pool

To create an instance pool:

  1. Create a virtual network with a subnet.
  2. Create an instance pool.

Create a virtual network with a subnet

To place multiple instance pools inside the same virtual network, see the following articles:

Create an instance pool

After completing the previous steps, you are ready to create an instance pool.

The following restrictions apply to instance pools:

  • Only General Purpose and Gen5 are available in public preview.
  • Pool name can contain only lowercase, numbers and hyphen, and can't start with a hyphen.
  • To get the subnet ID, use Get-AzVirtualNetworkSubnetConfig -Name "miPoolSubnet" -VirtualNetwork $virtualNetwork.
  • If you want to use AHB (Azure Hybrid Benefit), it is applied at the instance pool level. You can set the license type during pool creation or update it anytime after creation.

Important

Deploying an instance pool is a long running operation that takes approximately 4.5 hours.

To create an instance pool:

$instancePool = New-AzSqlInstancePool `
  -ResourceGroupName "myResourceGroup" `
  -Name "mi-pool-name" `
  -SubnetId "/subscriptions/subscriptionID/resourceGroups/myResourceGroup/providers/Microsoft.Network/virtualNetworks/miPoolVirtualNetwork/subnets/miPoolSubnet" `
  -LicenseType "LicenseIncluded" `
  -VCore 80 `
  -Edition "GeneralPurpose" `
  -ComputeGeneration "Gen5" `
  -Location "westeurope"

Important

Because deploying an instance pool is a long running operation, you need to wait until it completes before running any of the following steps in this article.

Create a managed instance inside the pool

After the successful deployment of the instance pool, it's time to create an instance inside it.

To create a managed instance, execute the following command:

$instanceOne = $instancePool | New-AzSqlInstance -Name "mi-pool-name" -VCore 2 -StorageSizeInGB 256

Deploying an instance inside a pool takes a couple of minutes. After the first instance has been created, additional instances can be created:

$instanceTwo = $instancePool | New-AzSqlInstance -Name "mi-pool-name" -VCore 4 -StorageSizeInGB 512

Create a database inside an instance

To create and manage databases in a managed instance that's inside a pool, use the single instance commands.

To create a database inside a managed instance:

$poolinstancedb = New-AzSqlInstanceDatabase -Name "mipooldb1" -InstanceName "poolmi-001" -ResourceGroupName "myResourceGroup"

Get instance pool usage

To get a list of instances inside a pool:

$instancePool | Get-AzSqlInstance

To get pool resource usage:

$instancePool | Get-AzSqlInstancePoolUsage

To get detailed usage overview of the pool and instances inside it:

$instancePool | Get-AzSqlInstancePoolUsage –ExpandChildren

To list the databases in an instance:

$databases = Get-AzSqlInstanceDatabase -InstanceName "pool-mi-001" -ResourceGroupName "resource-group-name"

Note

There is a limit of 100 databases per pool (not per instance).

Scale a managed instance inside a pool

After populating a managed instance with databases, you may hit instance limits regarding storage or performance. In that case, if pool usage has not been exceeded, you can scale your instance. Scaling a managed instance inside a pool is an operation that takes a couple of minutes. The prerequisite for scaling is available vCores and storage on the instance pool level.

To update the number of vCores and storage size:

$instanceOne | Set-AzSqlInstance -VCore 8 -StorageSizeInGB 512 -InstancePoolName "mi-pool-name"

To update storage size only:

$instance | Set-AzSqlInstance -StorageSizeInGB 1024 -InstancePoolName "mi-pool-name"

Connect to a managed instance inside a pool

To connect to a managed instance in a pool, the following two steps are required:

  1. Enable the public endpoint for the instance.
  2. Add an inbound rule to the network security group (NSG).

After both steps are complete, you can connect to the instance by using a public endpoint address, port, and credentials provided during instance creation.

Enable the public endpoint for the instance

Enabling the public endpoint for an instance can be done through the Azure portal or by using the following PowerShell command:

$instanceOne | Set-AzSqlInstance -InstancePoolName "pool-mi-001" -PublicDataEndpointEnabled $true

This parameter can be set during instance creation as well.

Add an inbound rule to the network security group

This step can be done through the Azure portal or using PowerShell commands, and can be done anytime after the subnet is prepared for the managed instance.

For details, see Allow public endpoint traffic on the network security group.

Move an existing single instance inside an instance pool

Moving instances in and out of a pool is one of the public preview limitations. A workaround that can be used relies on point-in-time restore of databases from an instance outside a pool to an instance that's already in a pool.

Both instances must be in the same subscription and region. Cross-region and cross-subscription restore is not currently supported.

This process does have a period of downtime.

To move existing databases:

  1. Pause workloads on the managed instance you are migrating from.

  2. Generate scripts to create system databases and execute them on the instance that's inside the instance pool.

  3. Do a point-in-time restore of each database from the single instance to the instance in the pool.

    $resourceGroupName = "my resource group name"
    $managedInstanceName = "my managed instance name"
    $databaseName = "my source database name"
    $pointInTime = "2019-08-21T08:51:39.3882806Z"
    $targetDatabase = "name of the new database that will be created"
    $targetResourceGroupName = "resource group of instance pool"
    $targetInstanceName = "pool instance name"
    
    Restore-AzSqlInstanceDatabase -FromPointInTimeBackup `
      -ResourceGroupName $resourceGroupName `
      -InstanceName $managedInstanceName `
      -Name $databaseName `
      -PointInTime $pointInTime `
      -TargetInstanceDatabaseName $targetDatabase `
      -TargetResourceGroupName $targetResourceGroupName `
      -TargetInstanceName $targetInstanceName
    
  4. Point your application to the new instance and resume it's workloads.

If there are multiple databases, repeat the process for each database.

Next steps