Use PowerShell to create a managed instance

Applies to: Azure SQL Managed Instance

This PowerShell script example creates a managed instance in a dedicated subnet within a new virtual network. It also configures a route table and a network security group for the virtual network. Once the script has been successfully run, the managed instance can be accessed from within the virtual network or from an on-premises environment. See Configure Azure VM to connect to Azure SQL Database Managed Instance and Configure a point-to-site connection to Azure SQL Managed Instance from on-premises.

Important

For limitations, see supported regions and supported subscription types.

Use Azure Cloud Shell

Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. You can use either Bash or PowerShell with Cloud Shell to work with Azure services. You can use the Cloud Shell preinstalled commands to run the code in this article, without having to install anything on your local environment.

To start Azure Cloud Shell:

Option	Example/Link
Select Try It in the upper-right corner of a code block. Selecting Try It doesn't automatically copy the code to Cloud Shell.
Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser.
Select the Cloud Shell button on the menu bar at the upper right in the Azure portal.

To run the code in this article in Azure Cloud Shell:

1. Start Cloud Shell.

2. Select the Copy button on a code block to copy the code.

3. Paste the code into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux, or by selecting Cmd+Shift+V on macOS.

4. Select Enter to run the code.

If you choose to install and use PowerShell locally, this tutorial requires Azure PowerShell 1.4.0 or later. If you need to upgrade, see Install Azure PowerShell module. If you are running PowerShell locally, you also need to run Connect-AzAccount to create a connection with Azure.

Sample script

PowerShell

# <SetVariables>
$NSnetworkModels = "Microsoft.Azure.Commands.Network.Models"
$NScollections = "System.Collections.Generic"

# The SubscriptionId in which to create these objects
$SubscriptionId = '<Enter subscription ID>'
# Set the resource group name and location for your managed instance
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "eastus2"
# 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'

# </SetVariables>

# <CreateResourceGroup>

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

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

# </CreateResourceGroup>

# <CreateVirtualNetwork>

# 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


# </CreateVirtualNetwork>

# <CreateManagedInstance>

# 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

# </CreateManagedInstance>

# <CreateDatabase>

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

New-AzSqlInstanceDatabase @databaseParams

# </CreateDatabase>

# Clean up deployment 
# Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

Clean up deployment

Use the following command to remove the resource group and all resources associated with it.

PowerShell

Remove-AzResourceGroup -ResourceGroupName $resourcegroupname

Script explanation

This script uses some of the following commands. For more information about used and other commands in the table below, click on the links to command specific documentation.

Command	Notes
New-AzResourceGroup	Creates a resource group in which all resources are stored.
New-AzVirtualNetwork	Creates a virtual network.
Add-AzVirtualNetworkSubnetConfig	Adds a subnet configuration to a virtual network.
Get-AzVirtualNetwork	Gets a virtual network in a resource group.
Set-AzVirtualNetwork	Sets the goal state for a virtual network.
Get-AzVirtualNetworkSubnetConfig	Gets a subnet in a virtual network.
Set-AzVirtualNetworkSubnetConfig	Configures the goal state for a subnet configuration in a virtual network.
New-AzRouteTable	Creates a route table.
Get-AzRouteTable	Gets route tables.
Set-AzRouteTable	Sets the goal state for a route table.
New-AzSqlInstance	Creates a managed instance.
New-AzSqlInstanceDatabase	Creates a database for your managed instance.
Remove-AzResourceGroup	Deletes a resource group, including all nested resources.

Next steps

For more information on Azure PowerShell, see Azure PowerShell documentation.

Additional PowerShell script samples for Azure SQL Managed Instance can be found in Azure SQL Managed Instance PowerShell scripts.