Create an instance pool (preview) - Azure SQL Managed Instance
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:
- An existing virtual network with an appropriately sized subnet range.
- The latest Az.SQL module for the current version of PowerShell or the latest version of the Azure CLI.
- Reviewed Instance and pool properties.
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:
- Failover groups. Failover rights aren't available to instances in a pool.
- Start/Stop.
- Zone Redundancy.
- Reserved capacity instance pricing isn't available.
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.
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
Feedback
https://aka.ms/ContentUserFeedback.
Kommer snart: I hele 2024 udfaser vi GitHub-problemer som feedbackmekanisme for indhold og erstatter det med et nyt feedbacksystem. Du kan få flere oplysninger under:Indsend og få vist feedback om