Skip to content

Latest commit

 

History

History
600 lines (413 loc) · 28.8 KB

instance-pools-configure.md

File metadata and controls

600 lines (413 loc) · 28.8 KB
Raw
title titleSuffix description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic ms.custom
Create instance pool
Azure SQL Managed Instance
Learn how to create instance pools for Azure SQL Managed Instance, a feature that lets you share resources for multiple instances, and provides a convenient and cost-efficient way to migrate smaller SQL Server databases to the cloud at scale. Create your instance pool by using the Azure portal, PowerShell, or the Azure CLI.
MariDjo
dmarinkovic
mathoma, randolphwest
10/14/2024
azure-sql-managed-instance
deployment-configuration
how-to
devx-track-azurepowershell
devx-track-azurecli
ignite-2024

Create an instance pool - Azure SQL Managed Instance

[!INCLUDE appliesto-sqlmi]

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

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.

Prerequisites

To create an instance pool, you should have:

Subnet size considerations

Carefully plan the size of your subnet when you 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 that contains 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 a pool is 40.

Create instance pool

You can create an instance pool by using the Azure portal, 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.

Azure portal

To create an instance pool in the Azure portal, follow these steps:

  1. Search for instance pools in the Azure portal and select the Instance pools service to open the Instance pools page:

    :::image type="content" source="media/instance-pools-configure/instance-pool-search-portal.png" alt-text="Screenshot searching for instance pools in the Azure portal." lightbox="media/instance-pools-configure/instance-pool-search-portal.png":::

  2. On the Instance pools page, select + Create to open the Create Azure SQL Managed Instance Pool page:

    :::image type="content" source="media/instance-pools-configure/instance-pool-portal-page.png" alt-text="Screenshot of the Instance pools page in the Azure portal, with +Create selected." lightbox="media/instance-pools-configure/instance-pool-portal-page.png":::

  3. On the Create Azure SQL Managed Instance Pool:

    1. Provide project and instance details on the Basics tab.
    2. Use Configure instance pool under Compute + storage to open the Compute + Storage page and choose the service tier, compute hardware and SQL Server license that you want the pool to use. Use Apply to save your compute settings and go back to the Create Azure SQL Managed Instance Pool page.
    3. Select an existing virtual network, or configure a new virtual network on the Networking tab.
    4. (Optional) Configure a non-default maintenance window for the pool on the Additional settings tab.
    5. Review your configuration on the Review + create tab, and then select Create to create your instance pool.

    :::image type="content" source="media/instance-pools-configure/instance-pool-create-portal-page.png" alt-text="Screenshot of the Create Azure SQL Managed Instance Pool page in the Azure portal, with Configure instance pool selected." lightbox="media/instance-pools-configure/instance-pool-create-portal-page.png":::

  4. You can monitor pool deployment from Notifications.

After your instance pool is created, you can create a new instance in the pool by using the Azure portal, or you can move an existing instance into the pool by using PowerShell or the Azure CLI.

PowerShell

To create your instance pool, use New-AzSqlInstancePool.

Consider the following:

  • For LicenseType, use BasePrice for the Azure Hybrid Benefit or LicenseIncluded if you 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:

$virtualNetwork = Get-AzVirtualNetwork -Name "<vNetName>" -ResourceGroupName "<resourceGroupName>"
$miSubnet = Get-AzVirtualNetworkSubnetConfig -Name "<miSubnetName>" -VirtualNetwork
$virtualNetwork$miSubnetConfigId = $miSubnet.Id

$parameters = @{
    ResourceGroupName = "<resource group name>"
    Name = "<instance pool name>"
    LicenseType = "LicenseIncluded"
    VCore = 8
    Edition = "GeneralPurpose"
    ComputeGeneration = "Gen5"
    Location = "<region>"
    $virtualNetwork = Get-AzVirtualNetwork -Name "<vNetName>" -ResourceGroupName "<resourceGroupName>"
    $miSubnet = Get-AzVirtualNetworkSubnetConfig -Name "<miSubnetName>" -VirtualNetwork $virtualNetwork
    $miSubnetConfigId = $miSubnet.Id
    SubnetId = $miSubnetConfigId
}

$instancePool = New-AzSqlInstancePool @parameters

Azure CLI

To create your instance pool, use az sql instance-pool create.

Consider the following:

  • For --license-type, use BasePrice for the Azure Hybrid Benefit or LicenseIncluded if you don't have a SQL Server license that can be used for the Azure Hybrid Benefit discount.

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

# Create the instance pool
az sql instance-pool create \
    --license-type LicenseIncluded \
    --location <region> \
    --name <pool name> \
    --capacity 8 \
    --tier GeneralPurpose \
    --family Gen5 \
    --resource-group <resource group name> \
    --subnet <subnet name> \
    --vnet-name <vnet name>

Create new instance inside pool

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

Consider the following:

  • You must specify the license type for the new instance, and it must match the license type of the pool.

Azure portal

To create a new instance inside a pool by using the Azure portal, follow these steps:

  1. Go to the Azure SQL page in the Azure portal.

  2. On the Azure SQL page, select + Create to open the Select SQL deployment option.

  3. On the SQL managed instances tile, choose Single instance as the resource type and then select Create to open the Create Azure SQL Managed Instance page.

  4. On the Basics tab of the Create Azure SQL Managed Instance page:

    1. Select the resource group that contains your existing instance pool.
    2. Choose Yes to Belongs to an instance pool? under Managed Instance details to create your new instance inside an instance pool.
    3. Select the pool from the Instance pool dropdown list.

    :::image type="content" source="media/instance-pools-configure/create-instance-inside-pool.png" alt-text="Screenshot of the Create Azure SQL Managed Instance page in the Azure portal with belongs to an instance pool selected." lightbox="media/instance-pools-configure/create-instance-inside-pool.png":::

    After you've selected an instance pool from the dropdown list, you see the compute cost for the instance change to 0 because compute is included in the cost of the pool.

  5. Fill out the remaining details on the Create Azure SQL Managed Instance page to create your instance inside the pool. For details, review Create Azure SQL Managed Instance.

  6. Select Review + create to review settings for your new instance and then use Create to deploy your instance inside the selected pool.

PowerShell

To identify pool parameters with PowerShell, 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>

$instance01Params = @{
    Name = $instance01
    VCore = 2
    StorageSizeInGB = 32
    AdministratorCredential = $adminCredential
}

$instance01 = $instancePool | New-AzSqlInstance @instance01Params

Azure CLI

To create a new instance in your pool with the Azure CLI, provide the pool name in the --instance-pool-name parameter when you use az sql mi create to create your instance:

#obtain the subnetId of an instance pool
sqlmipoolSubnetId=$(az sql instance-pool show -g <resource group name> -n <instance pool name> --query subnetId --output tsv)

az sql mi create \
  --license-type LicenseIncluded \
  --name <Instance name> \
  --admin-user <username> \
  --admin-password <password> \
  --capacity 2 \
  --instance-pool-name <instance pool name> \
  --storage 32 \
  --resource-group <resource group name> \
  --subnet $sqlmipoolSubnetId

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.

Moving an existing instance inside a pool by using the Azure portal is not currently supported.

PowerShell

To move an instance into a pool with PowerShell, 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 ''

Azure CLI

To move an instance into a pool with the Azure CLI, provide the pool name in the --instance-pool-name parameter when you use az sql mi update to update your instance:

az sql mi update \
  --name <instance name> \
  --instance-pool-name <instance pool name> \
  --resource-group <resource group name>

To move an instance out of a pool, provide a blank name in the --instance-pool-name parameter when you use az sql mi update to update your instance:

az sql mi update \
  --name <instance name> \
  --instance-pool-name '' \
  --resource-group <resource group name>

Connect to instance in a pool

You can choose to connect to an instance in a pool with either a private endpoint, or a public endpoint. To use a private endpoint, you'll need to use the Azure Private Link.

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

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 the Azure portal, PowerShell or the Azure CLI.

Azure portal

To create a new database for an existing SQL managed instance by using the Azure portal, follow these steps:

  1. Go to your SQL managed instance in the Azure portal.
  2. On the Overview pane, select + New database from the command bar to open the Create Azure SQL Managed Database page.
  3. Provide details for the new database.
  4. Select Review + create to review your new database configuration and then use Create to deploy your database.

PowerShell

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

$databaseParams = @{
    Name = "<database name>"
    InstanceName = "<instance name>"
    ResourceGroupName = "<resource group>"
}

New-AzSqlInstanceDatabase @databaseParams

Azure CLI

To create a new database for your instance, use az sql midb create:

az sql midb create
   --managed-instance <Instance name> \
   --name <Database name> \
   --resource-group <Resource group name>

Get pool usage

You can determine how resources are being used by resources in a pool by using the Azure portal, PowerShell, or the Azure CLI.

Azure portal

To get a list of instances inside a pool, use the Azure portal to view the Instance pools page. Select the pool name to view the instances inside the pool on the Overview page:

:::image type="content" source="media/instance-pools-configure/instance-pool-usage.png" alt-text="Screenshot of the Overview page of an instance pool in the Azure portal." lightbox="media/instance-pools-configure/instance-pool-usage.png":::

PowerShell

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:

$databaseParams = @{
    InstanceName = $instance01Name
    ResourceGroupName = $resourceGroupName
}

$databases = Get-AzSqlInstanceDatabase @databaseParams

Note

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

Azure CLI

To get information about the instances and resource usage in the pool, use az sql instance-pool show:

sqlmipoolId=$(az sql instance-pool show --name <pool name> \
--resource-group <resource group name> --query id | cut -d '"' -f 2) \
az sql mi list --resource-group <resource group name> \
--query "[?instancePoolId == '$sqlmipoolId'].{sqlmiName:name}" -o tsv

Update an instance pool

You can update settings for an existing instance pool by using the Azure portal, PowerShell or the Azure CLI.

Azure portal

Use the Compute + storage pane under Settings of the Instance pool page in the Azure portal to update the license type, vCore size, and hardware type for your pool:

:::image type="content" source="media/instance-pools-configure/instance-compute-storage.png" alt-text="Screenshot of the Compute + storage Instance pool pane in the Azure portal." lightbox="media/instance-pools-configure/instance-compute-storage.png":::

Use the Maintenance pane under Settings of the Instance pool page in the Azure portal to update the maintenance window for your pool:

:::image type="content" source="media/instance-pools-configure/instance-maintenance.png" alt-text="Screenshot of the Maintenance Instance pool pane in the Azure portal." lightbox="media/instance-pools-configure/instance-maintenance.png":::

PowerShell

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:

$instancePoolParams = @{
    LicenseType = "BasePrice"
    VCores = 16
    ComputeGeneration = "Gen8"
}
$instancePool | Set-AzSqlInstancePool @instancePoolParams

You can also determine the available maintenance window schedules:

$parameters = @{
    Location = $location
    MaintenanceScope = "SQLManagedInstance"
}

$configurations = Get-AzMaintenancePublicConfiguration @parameters
$maintenanceWindowOptions = $configurations | Where-Object { $_.Location -eq $location -and $_.MaintenanceScope -eq "SQLManagedInstance" }

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

$instancePoolParams = @{
    MaintenanceConfigurationId = $maintenanceWindowOptions[1].Id
}

$instancePool | Set-AzSqlInstancePool @instancePoolParams

Azure CLI

To update configuration settings for your pool, use az sql instance-pool update:

az sql instance-pool update --name <pool name> \
--resource-group <resource group> --capacity 16 \
--license-type LicenseIncluded --family Gen8IM

To update the maintenance window:

maintenanceWindowOptions=$(az maintenance public-configuration list \
--query "[?location==<eastus2>&&contains(maintenanceScope,'SQLManagedInstance')]")

az sql instance-pool update --name <pool name> \
--resource-group <resource group> --maint-config-id <maintenance configuration 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,

PowerShell

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

The following sample updates the vCores to 8 and changes the storage size to 512 GB for Instance1:

$instancePoolParams = @{
    VCore = 8
    StorageSizeInGB = 512
    InstancePoolName = $instancePoolName
}

$instance1name | Set-AzSqlInstance @instancePoolParams

Azure CLI

To modify resource parameters for an instance inside a pool, use az sql mi update.

The following sample updates the vCores to 8 and changes the storage size to 512 GB for Instance1:

az sql mi update \
  --name Instance1 \
  --resource-group <resource group name> \
  --capacity 8 \
  --storage 512

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.

PowerShell

To delete an instance pool, use Remove-AzSqlInstancePool.

The following sample script deletes an empty instance pool:

$params = @{
    ResourceGroupName = "<resource group name>"
    Name = "<instance pool name>"
}

Remove-AzSqlInstancePool @params

Azure CLI

To delete an instance pool, use az sql instance-pool delete.

The following sample script deletes an empty instance pool:

az sql instance-pool delete
  --name <pool name>
  --resource group <resource group name>

Instance pool operations

The following table shows available instance pool operations:

Command Azure portal PowerShell Azure CLI
Create an instance pool Yes Yes Yes
Update pool properties Yes Yes Yes
Check a pool use and properties Yes Yes Yes
Delete an instance pool Yes Yes Yes
Create new managed instance inside a pool Yes 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

PowerShell

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.

Azure CLI

Prepare your environment for the Azure CLI.

[!INCLUDE azure-cli-prepare-your-environment-no-header]

Available Azure CLI commands:

Cmdlet Description
az sql instance-pool create Creates an instance pool.
az sql instance-pool show Returns information about an instance pool.
az sql instance-pool update Sets or updates properties for an instance pool.
az sql instance-pool delete Removes an instance pool.

Limitations

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.
  • Moving an instance pool to another subnet is not supported.
  • Only General Purpose service tier is available. The Next-gen General Purpose and Business Critical service tiers aren't available.
  • Only the standard-series (Gen5) or premium-series hardware is supported. The premium-series memory optimized hardware isn't supported.
  • 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 move instances in and out of the pool. Use PowerShell or the Azure CLI instead.
  • The following SQL Managed Instance features aren't supported on instances in a pool:

Support requests

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

To create a new support request in the Azure portal, follow these steps:

  1. Open the New support request page in the Azure portal.

  2. On the New support request, provide the following information:

    1. For Issue type, select Technical.
    2. Choose the appropriate Subscription from the dropdown list.
    3. For the Service type, select SQL Managed Instance.
    4. For Resource, provide the name of your SQL Managed Instance if it exists, or select General question if you're not able to deploy your instance inside the pool.
    5. For Summary, type instance pools.
    6. For Problem type, choose Create, Scale, Stop, Start, or Delete Resources.
    7. For Problem Subtype, choose Instance Pools.

    :::image type="content" source="media/instance-pools-configure/support-request.png" alt-text="Screenshot of the Instance pools support request in the Azure portal." lightbox="media/instance-pools-configure/support-request.png":::

  3. Select Next on the subsequent pages until you're able to Create your support request.

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