Quickstart: Create Azure SQL Managed Instance

Article
10/14/2024

Applies to: Azure SQL Managed Instance

This quickstart teaches you to create a deployment of Azure SQL Managed Instance by using the Azure portal, PowerShell, and the Azure CLI.

Prerequisites

An Azure subscription. If you don't have an Azure subscription, create a trial account
The latest version of the Az.SQL PowerShell module or the latest version of the Azure CLI.

For limitations, see Supported regions.

Create Azure SQL Managed Instance

You can create a deployment of Azure SQL Managed Instance by using the Azure portal, PowerShell, and the Azure CLI.

Consider the following:

You can cancel the provisioning process through Azure portal, or via PowerShell or the Azure CLI or other tooling using the REST API.
Instance deployment delayed if it's impacted by other operations in the same subnet, such as a long-running restore or scaling an instance.
Read permissions for the resource group are required to see the managed instance in your resource group.

Important

Deploying a managed instance is a long-running operation. Deploying the first instance to a subnet typically takes much longer than deploying to a subnet with existing instances.

To create your instance in the Azure portal, you'll first need to sign into the Azure portal, and then fill out the information on the Create Azure SQL Managed Instance page.

To create your instance, follow these steps:

Sign in to the Azure portal.
Select Azure SQL on the left menu of the Azure portal. If Azure SQL isn't in the list, select All services, and then enter Azure SQL in the search box.
Select + Create to open the Select SQL deployment option page. You can view additional information about Azure SQL Managed Instance by selecting Show details on the SQL managed instances tile.
Choose Singe instance from the dropdown and then select Create to open the Create Azure SQL Managed Instance page.

Basics tab

Fill out mandatory information required on the Basics tab, which is the minimum requirement to provision a SQL Managed Instance.

The following table provides details for the required information on the Basics tab:

Setting	Suggested value	Description
Subscription	Your subscription.	A subscription that gives you permission to create new resources.
Resource group	A new or existing resource group.	For valid resource group names, see Naming rules and restrictions.
Managed instance name	Any valid name.	For valid names, see Naming rules and restrictions.
Region	The region in which you want to create the managed instance.	For information about regions, see Azure regions.
Authentication method	Use SQL authentication	For the purpose of this quickstart, use SQL authentication. But you can also choose to use both SQL and Microsoft Entra authentication.
Managed instance admin login	Any valid username.	For valid names, see Naming rules and restrictions. Don't use `serveradmin` because that's a reserved server-level role.
Password	Any valid password.	The password must be at least 16 characters long and meet the defined complexity requirements.

Under Managed Instance details, select Configure Managed Instance in the Compute + storage section to open the Compute + storage page.

Screenshot of the Create Azure SQL Managed Instance page with Configure Managed Instance selected.

The following table provides recommendations for the compute and storage for your sample SQL Managed Instance:

Setting	Suggested value	Description
Service Tier	General Purpose	The General Purpose tier is suitable for most production workloads, and is the default option. For more information, review resource limits.
Hardware generation	Standard-series (Gen5)	Standard-series (Gen5) is the default hardware generation, which defines compute and memory limits. Standard-series (Gen5) is the default.
vCores	Designate a value.	vCores represent the exact amount of compute resources that are always provisioned for your workload. Eight vCores is the default.
Storage in GB	Designate a value.	Storage size in GB, select based on expected data size.
SQL Server License	Select applicable licensing model.	Either pay as you go, use an existing SQL license with the Azure Hybrid Benefit, or enable the Hybrid failover rights
Backup storage redundancy	Geo-redundant backup storage.	Storage redundancy inside Azure for backup storage. Geo-redundant backup storage is default and recommended, though Zone and Local redundancy allow for greater cost flexibility and single region data residency.

Once you've designated your Compute + Storage configuration, use Apply to save your settings, and navigate back to the Create Azure SQL Managed Instance page. Select Next to move to the Networking tab

Networking tab

Fill out optional information on the Networking tab. If you omit this information, the portal will apply default settings.

The following table provides details for information on the Networking tab:

Setting	Suggested value	Description
Virtual network / subnet	Create new, or use an existing virtual network	If a network or subnet is unavailable, it must be modified to satisfy the network requirements before you select it as a target for the new managed instance.
Connection type	Choose a suitable connection type.	For more information, see connection types.
Public endpoint	Select Disable.	For a managed instance to be accessible through the public data endpoint, you need to enable this option.
Allow access from (if Public endpoint is enabled)	Select No Access	The portal configures the security group with a public endpoint. Based on your scenario, select one of the following options: Azure services: We recommend this option when you're connecting from Power BI or another multitenant service. Internet: Use for test purposes when you want to quickly spin up a managed instance. Not recommended for production environments. No access: This option creates a Deny security rule. Modify this rule to make a managed instance accessible through a public endpoint. For more information on public endpoint security, see Using Azure SQL Managed Instance securely with a public endpoint.

Select Review + create to review your choices before you create a managed instance. Or, configure security settings by selecting Next: Security settings.

Security tab

For the purpose of this quickstart, leave the settings on the Security tab at their default values.

Select Review + create to review your choices before you create a managed instance. Or, configure more custom settings by selecting Next: Additional settings.

Additional settings

Fill out optional information on the Additional settings tab. If you omit this information, the portal applies default settings.

The following table provides details for information on the Additional settings tab:

Setting	Suggested value	Description
Collation	Choose the collation that you want to use for your managed instance. If you migrate databases from SQL Server, check the source collation by using `SELECT SERVERPROPERTY(N'Collation')` and use that value.	For information about collations, see Set or change the server collation.
Time zone	Select the time zone that managed instance will observe.	For more information, see Time zones.
Geo-Replication	Select No.	Only enable this option if you plan to use the managed instance as a failover group secondary.
Maintenance window	Choose a suitable maintenance window.	Designate a[schedule for when your instance is maintained by the service.

Select Review + create to review your choices before you create a managed instance. Or, configure Azure Tags by selecting Next: Tags (recommended).

Review + create

On the Review + create tab, review your choices, and then select Create to deploy your managed instance.

Monitor deployment progress

Select the Notifications icon to view the status of the deployment.
Select Deployment in progress in the notification to open the SQL Managed Instance window and further monitor the deployment progress.

Once deployment completes, navigate to your resource group to view your managed instance:

Screenshot of the SQL Managed Instance resources in the Azure portal.

Tip

If you closed your web browser or moved away from the deployment progress screen, you can monitor the provisioning operation via the managed instance's Overview page in the Azure portal, PowerShell or the Azure CLI.

Use PowerShell to create your instance. For details, review Create SQL Managed Instance with PowerShell.

First, set your variables:

$NSnetworkModels = "Microsoft.Azure.Commands.Network.Models"
$NScollections = "System.Collections.Generic"

Connect-AzAccount -Environment AzureChinaCloud
# The SubscriptionId in which to create these objects
$SubscriptionId = ''
# Set the resource group name and location for your managed instance
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "chinanorth3"
# Set the networking values for your managed instance
$vNetName = "myVnet-$(Get-Random)"
$vNetAddressPrefix = "10.0.0.0/16"
$defaultSubnetName = "myDefaultSubnet-$(Get-Random)"
$defaultSubnetAddressPrefix = "10.0.0.0/24"
$miSubnetName = "MISubnet-$(Get-Random)"
$miSubnetAddressPrefix = "10.0.0.0/24"
#Set the managed instance name for the new managed instance
$instanceName = "mi-name-$(Get-Random)"
# Set the admin login and password for your managed instance
$miAdminSqlLogin = "SqlAdmin"
$miAdminSqlPassword = "ChangeThisPassword!!"
# Set the managed instance service tier, compute level, and license mode
$edition = "General Purpose"
$vCores = 8
$maxStorage = 256
$computeGeneration = "Gen5"
$license = "LicenseIncluded" #"BasePrice" or LicenseIncluded if you have don't have SQL Server licence that can be used for AHB discount
$dbname = 'SampleDB'

Next, create your resource group:

# Set subscription context
$subscriptionContextParams = @{
    SubscriptionId = $SubscriptionId
}
Set-AzContext @subscriptionContextParams

# Create a resource group
$resourceGroupParams = @{
    Name = $resourceGroupName
    Location = $location
    Tag = @{Owner="SQLDB-Samples"}
}
$resourceGroup = New-AzResourceGroup @resourceGroupParams

After that, create your virtual network:

# Configure virtual network, subnets, network security group, and routing table
$networkSecurityGroupParams = @{
    Name = 'myNetworkSecurityGroupMiManagementService'
    ResourceGroupName = $resourceGroupName
    Location = $location
}
$networkSecurityGroupMiManagementService = New-AzNetworkSecurityGroup @networkSecurityGroupParams

$routeTableParams = @{
    Name = 'myRouteTableMiManagementService'
    ResourceGroupName = $resourceGroupName
    Location = $location
}
$routeTableMiManagementService = New-AzRouteTable @routeTableParams

$virtualNetworkParams = @{
    ResourceGroupName = $resourceGroupName
    Location = $location
    Name = $vNetName
    AddressPrefix = $vNetAddressPrefix
}

$virtualNetwork = New-AzVirtualNetwork @virtualNetworkParams

$subnetConfigParams = @{
    Name = $miSubnetName
    VirtualNetwork = $virtualNetwork
    AddressPrefix = $miSubnetAddressPrefix
    NetworkSecurityGroup = $networkSecurityGroupMiManagementService
    RouteTable = $routeTableMiManagementService
}

$subnetConfig = Add-AzVirtualNetworkSubnetConfig @subnetConfigParams | Set-AzVirtualNetwork

$virtualNetwork = Get-AzVirtualNetwork -Name $vNetName -ResourceGroupName $resourceGroupName

$subnet= $virtualNetwork.Subnets[0]

# Create a delegation
$subnet.Delegations = New-Object "$NScollections.List``1[$NSnetworkModels.PSDelegation]"
$delegationName = "dgManagedInstance" + (Get-Random -Maximum 1000)
$delegationParams = @{
    Name = $delegationName
    ServiceName = "Microsoft.Sql/managedInstances"
}
$delegation = New-AzDelegation @delegationParams
$subnet.Delegations.Add($delegation)

Set-AzVirtualNetwork -VirtualNetwork $virtualNetwork

$miSubnetConfigId = $subnet.Id

$allowParameters = @{
    Access = 'Allow'
    Protocol = 'Tcp'
    Direction= 'Inbound'
    SourcePortRange = '*'
    SourceAddressPrefix = 'VirtualNetwork'
    DestinationAddressPrefix = '*'
}
$denyInParameters = @{
    Access = 'Deny'
    Protocol = '*'
    Direction = 'Inbound'
    SourcePortRange = '*'
    SourceAddressPrefix = '*'
    DestinationPortRange = '*'
    DestinationAddressPrefix = '*'
}
$denyOutParameters = @{
    Access = 'Deny'
    Protocol = '*'
    Direction = 'Outbound'
    SourcePortRange = '*'
    SourceAddressPrefix = '*'
    DestinationPortRange = '*'
    DestinationAddressPrefix = '*'
}

$networkSecurityGroupParams = @{
    ResourceGroupName = $resourceGroupName
    Name = "myNetworkSecurityGroupMiManagementService"
}

$networkSecurityGroup = Get-AzNetworkSecurityGroup @networkSecurityGroupParams

$allowRuleParams = @{
    Access = 'Allow'
    Protocol = 'Tcp'
    Direction = 'Inbound'
    SourcePortRange = '*'
    SourceAddressPrefix = 'VirtualNetwork'
    DestinationAddressPrefix = '*'
}

$denyInRuleParams = @{
    Access = 'Deny'
    Protocol = '*'
    Direction = 'Inbound'
    SourcePortRange = '*'
    SourceAddressPrefix = '*'
    DestinationPortRange = '*'
    DestinationAddressPrefix = '*'
}

$denyOutRuleParams = @{
    Access = 'Deny'
    Protocol = '*'
    Direction = 'Outbound'
    SourcePortRange = '*'
    SourceAddressPrefix = '*'
    DestinationPortRange = '*'
    DestinationAddressPrefix = '*'
}

$networkSecurityGroup |
    Add-AzNetworkSecurityRuleConfig @allowRuleParams -Priority 1000 -Name "allow_tds_inbound" -DestinationPortRange 1433 |
    Add-AzNetworkSecurityRuleConfig @allowRuleParams -Priority 1100 -Name "allow_redirect_inbound" -DestinationPortRange 11000-11999 |
    Add-AzNetworkSecurityRuleConfig @denyInRuleParams -Priority 4096 -Name "deny_all_inbound" |
    Add-AzNetworkSecurityRuleConfig @denyOutRuleParams -Priority 4096 -Name "deny_all_outbound" |
    Set-AzNetworkSecurityGroup

And finally, create your instance:

# Create credentials
$secpassword = ConvertTo-SecureString $miAdminSqlPassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential -ArgumentList @($miAdminSqlLogin, $secpassword)

$managedInstanceParams = @{
    Name = $instanceName
    ResourceGroupName = $resourceGroupName
    Location = $location
    SubnetId = $miSubnetConfigId
    AdministratorCredential = $credential
    StorageSizeInGB = $maxStorage
    VCore = $vCores
    Edition = $edition
    ComputeGeneration = $computeGeneration
    LicenseType = $license
}

New-AzSqlInstance @managedInstanceParams

Use the Azure CLI to create your instance. For details, review Create SQL Managed Instance with the Azure CLI.

First, set your variables:

# Variable block
let "randomIdentifier=$RANDOM*$RANDOM"
location="China North 3"
resourceGroup="msdocs-azuresql-rg-$randomIdentifier"
tag="create-managed-instance"
vNet="msdocs-azuresql-vnet-$randomIdentifier"
subnet="msdocs-azuresql-subnet-$randomIdentifier"
nsg="msdocs-azuresql-nsg-$randomIdentifier"
route="msdocs-azuresql-route-$randomIdentifier"
instance="msdocs-azuresql-instance-$randomIdentifier"
login="azureuser"
password="Pa$$w0rD-$randomIdentifier"
dbname="SampleDB"

echo "Using resource group $resourceGroup with login: $login, password: $password..."

Next, create your resource group:

echo "Creating $resourceGroup in $location..."
az group create --name $resourceGroup --location "$location" --tags $tag

After that, create your virtual network:

echo "Creating $vNet with $subnet..."
az network vnet create --name $vNet --resource-group $resourceGroup --location "$location" --address-prefixes 10.0.0.0/16
az network vnet subnet create --name $subnet --resource-group $resourceGroup --vnet-name $vNet --address-prefixes 10.0.0.0/24 --delegations Microsoft.Sql/managedInstances

echo "Creating $nsg..."
az network nsg create --name $nsg --resource-group $resourceGroup --location "$location"

az network nsg rule create --name "allow_management_inbound" --nsg-name $nsg --priority 100 --resource-group $resourceGroup --access Allow --destination-address-prefixes 10.0.0.0/24 --destination-port-ranges 9000 9003 1438 1440 1452 --direction Inbound --protocol Tcp --source-address-prefixes "*" --source-port-ranges "*"
az network nsg rule create --name "allow_misubnet_inbound" --nsg-name $nsg --priority 200 --resource-group $resourceGroup --access Allow --destination-address-prefixes 10.0.0.0/24 --destination-port-ranges "*" --direction Inbound --protocol "*" --source-address-prefixes 10.0.0.0/24 --source-port-ranges "*"
az network nsg rule create --name "allow_health_probe_inbound" --nsg-name $nsg --priority 300 --resource-group $resourceGroup --access Allow --destination-address-prefixes 10.0.0.0/24 --destination-port-ranges "*" --direction Inbound --protocol "*" --source-address-prefixes AzureLoadBalancer --source-port-ranges "*"
az network nsg rule create --name "allow_management_outbound" --nsg-name $nsg --priority 1100 --resource-group $resourceGroup --access Allow --destination-address-prefixes AzureCloud --destination-port-ranges 443 12000 --direction Outbound --protocol Tcp --source-address-prefixes 10.0.0.0/24 --source-port-ranges "*"
az network nsg rule create --name "allow_misubnet_outbound" --nsg-name $nsg --priority 200 --resource-group $resourceGroup --access Allow --destination-address-prefixes 10.0.0.0/24 --destination-port-ranges "*" --direction Outbound --protocol "*" --source-address-prefixes 10.0.0.0/24 --source-port-ranges "*"

echo "Creating $route..."
az network route-table create --name $route --resource-group $resourceGroup --location "$location"

az network route-table route create --address-prefix 0.0.0.0/0 --name "primaryToMIManagementService" --next-hop-type Internet --resource-group $resourceGroup --route-table-name $route
az network route-table route create --address-prefix 10.0.0.0/24 --name "ToLocalClusterNode" --next-hop-type VnetLocal --resource-group $resourceGroup --route-table-name $route

echo "Configuring $subnet with $nsg and $route..."
az network vnet subnet update --name $subnet --network-security-group $nsg --route-table $route --vnet-name $vNet --resource-group $resourceGroup

And finally, create your instance:

# This step will take awhile to complete. You can monitor deployment progress in the activity log within the Azure portal.
echo "Creating $instance with $vNet and $subnet..."
az sql mi create --admin-password $password --admin-user $login --name $instance --resource-group $resourceGroup --subnet $subnet --vnet-name $vNet --location "$location"

Review network settings

Select the Route table resource in your resource group to review the default user-defined route table object and entries to route traffic from, and within, the SQL Managed Instance virtual network. To change or add routes, open the Routes in the Route table settings.

Select the Network security group object to review the inbound and outbound security rules. To change or add rules, open the Inbound Security Rules and Outbound security rules in the Network security group settings.

Screenshot of the Security rules for your instance in the Azure portal.

Important

If you enabled public endpoint for SQL Managed Instance, open ports to allow network traffic connections to SQL Managed Instance from the public internet.

Create database

You can create a new database by using the Azure portal, PowerShell, or the Azure CLI.

To create a new database for your instance in the Azure portal, follow these steps:

Go to your SQL managed instance in the Azure portal.
Select + New database on the Overview page for your SQL managed instance to open the Create Azure SQL Managed Database page.
Provide a name for the database on the Basics tab.
On the Data source tab, select None for an empty database, or restore a database from backup.
Configure the remaining settings on the remaining tabs, and then select Review + create to validate your choices.
Use Create to deploy your database.

Use PowerShell to create your database:

$databaseParams = @{
    ResourceGroupName = $resourceGroupName
    InstanceName = $instanceName
    Name = $dbname
    Collation = 'Latin1_General_100_CS_AS_SC'
}

New-AzSqlInstanceDatabase @databaseParams

Use the Azure CLI to create your database:

az sql midb create -g $resourceGroup --mi $instance -n $dbname --collation Latin1_General_100_CS_AS_SC

Retrieve connection details to SQL Managed Instance

To connect to SQL Managed Instance, follow these steps to retrieve the host name and fully qualified domain name (FQDN):

Return to the resource group and select the SQL managed instance object that was created.
On the Overview tab, locate the Host property. Copy the host name to your clipboard for the managed instance for use in the next quickstart by clicking the Copy to clipboard button.

The value copied represents a fully qualified domain name (FQDN) that can be used to connect to SQL Managed Instance. It's similar to the following address example: your_host_name.a1b2c3d4e5f6.database.chinacloudapi.cn.

Review the following related content:

To restore an existing SQL Server database from on-premises to SQL Managed Instance:

Use the Azure Database Migration Service for migration to restore from a database backup file.
Use the T-SQL RESTORE command to restore from a database backup file.

Next steps

Connect your applications to SQL Managed Instance.