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.
|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
|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.|
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:
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.
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:
Create a virtual network with a subnet
To place multiple instance pools inside the same virtual network, see the following articles:
- Determine VNet subnet size for an Azure SQL Database managed instance.
- Create new virtual network and subnet using the Azure portal template or follow the instructions for preparing an existing virtual network.
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.
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"
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"
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:
- Enable the public endpoint for the instance.
- 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:
Pause workloads on the managed instance you are migrating from.
Generate scripts to create system databases and execute them on the instance that's inside the instance pool.
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
Point your application to the new instance and resume it's workloads.
If there are multiple databases, repeat the process for each database.
- For a features and comparison list, see SQL common features.
- For more information about VNet configuration, see managed instance VNet configuration.
- For a quickstart that creates a managed instance and restores a database from a backup file, see create a managed instance.
- For a tutorial using the Azure Database Migration Service (DMS) for migration, see managed instance migration using DMS.
- For advanced monitoring of managed instance database performance with built-in troubleshooting intelligence, see Monitor Azure SQL Database using Azure SQL Analytics.
- For pricing information, see SQL Database managed instance pricing.