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

First published on MSDN on Jun 27, 2018

Azure is a fully managed 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 )

The following script creates new Managed Instance in your Azure :

# 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 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

.

 

This article was originally published by Microsoft's Azure SQL Database Blog. You can find the original article here.