Quick-start script: Create Azure SQL Managed Instance using PowerShell library

  • Article

Azure SQL Database Managed Instance is a fully managed SQL Server Database Engine hosted in Azure cloud. In this post you can find a simple PowerShell script that you can use to quickly create a new Managed Instance.

As a prerequisite, you would need to create VNET/subnet using setup quick start script and use the names of vNet, subnet, and resource group that you configured in that script. Make sure that you have installed Azure RM PowerShell .

Once you install everything and prepare environment, you can run the following script to create a Managed Instance. You just need to change the following parameters in the script below:

  • Environment where Managed Instance should be placed - Azure subscription id, VNET/subnet name, and resource group.
  • Managed Instance properties - sql login/password used to connect to the instance, size of the instance (cores/max storage)

The following script creates new Managed Instance in your Azure network:

 # Set parameters
$subscriptionId = "b4c5a924-14c1-4bde-e7b170769m3"
$resourceGroup = "..."
$location = "West Central US"
$vNetName = "..."
$subnetName = "..."
$instanceName = "myManagedInstance"
$miAdminSqlLogin = "mysqlloginname"
$miAdminSqlPassword = "some strong password"
$vCores = 16
$maxStorage = 256
$license = "BasePrice" # or LicenseIncluded if you have don't have SQL Server licence that can be used for AHB discount


# Setup subscription specific information
Select-AzureRmSubscription -Subscription $subscriptionId 
$vNet = Get-AzureRmVirtualNetwork -Name $vNetName -ResourceGroupName $resourceGroup
$subnet = Get-AzureRmVirtualNetworkSubnetConfig -Name $SubnetName -VirtualNetwork $vNet
$subnetId = $subnet.Id

$properties = New-Object System.Object
$properties | Add-Member -type NoteProperty -name subnetId -Value $subnet.Id
$sku = @{ tier="GeneralPurpose"; name="GP_Gen4"} #or GP_Gen5


$properties | Add-Member -type NoteProperty -name administratorLogin -Value $miAdminSqlLogin
$properties | Add-Member -type NoteProperty -name administratorLoginPassword -Value $miAdminSqlPassword
$properties | Add-Member -type NoteProperty -name vCores -Value $vCores
$properties | Add-Member -type NoteProperty -name storageSizeInGB -Value $maxStorage
$properties | Add-Member -type NoteProperty -name licenseType -Value $license

New-AzureRmResource -Location $location `
                    -Sku $sku `
                    -Properties $properties `
                    -ResourceName $instanceName `
                    -ResourceType "Microsoft.SQL/managedInstances" `
                    -ResourceGroupName $resourceGroup `
                    -Force -ApiVersion "2015-05-01-preview"

Instead of the core Azure.RM PowerShell library you can use Azure.Rm.Sql library that have commands specialized for Azure SQL Database. In this case, you would use New-AzureRmSqlManagedInstance command as shown in the following example:

 

 Select-AzureRmSubscription -Subscription "ee4em877-4751-9070-718f-67cd8273754b"

$resourceGroup = "my_rg"
$vNetName = "my_vnet"
$subnetName = "my_subnet"

$vNet = Get-AzureRmVirtualNetwork -Name $vNetName -ResourceGroupName $resourceGroup
$subnet = Get-AzureRmVirtualNetworkSubnetConfig -Name $SubnetName -VirtualNetwork $vNet
$subnetId = $subnet.Id


New-AzureRmSqlManagedInstance -Name "jovanpop-ps-test" `
-ResourceGroupName $resourceGroup -Location westcentralus -SubnetId $subnetId `
-AdministratorCredential (Get-Credential) `
-StorageSizeInGB 1024 -VCore 8 -Edition "GeneralPurpose" `
-ComputeGeneration Gen4 -LicenseType LicenseIncluded

(Get-Credential) will show a dialog where you should enter the username and password of Managed Instance Admin. Once you run this script you will see your managed instance.

Once the instance is created, you can change it's size using Update-AzureRmSqlManagedInstance command.

As an alternative approach, you can prepare ARM template and deploy the ARM template using PowerShell.