Creating Azure SQL Managed Instance using ARM templates

Azure API enables you to create Azure SQL Managed Instance using ARM templates. These are JSON objects that contain definition of resources that should be created. You can send these objects to the Azure REST API to automate creation of Azure SQL Managed Instance.

In order to create a new Azure SQL Managed Instance, you need to create ARM JSON request. An example of ARM JSON request is shown in the following script (the important part is under resources node):

{
    "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
    "contentVersion": "1.0.0.1",
    "parameters": {
        "pwd": {
            "type": "securestring"
        }
    },
    "resources": [
        {
            "name": "jovanpoptest",
            "location": "westcentralus",
            "tags": {"Owner":"JovanPop","Purpose":"Test"},
            "sku": {
                "name": "GP_Gen4"
            },
            "properties": {
                "administratorLogin": "Login that will connect to the instance",
                "administratorLoginPassword": "[parameters('pwd')]",
                "subnetId": "/subscriptions/ee5ea899-0791-9270-77cd8273794b/resourceGroups/cl_pilot/providers/Microsoft.Network/virtualNetworks/cl_pilot/subnets/CLean",
                "storageSizeInGB": "256",
                "vCores": "16",
                "licenseType": "BasePrice"
            },
            "type": "Microsoft.Sql/managedInstances",
            "identity": {
                "type": "SystemAssigned"
            },
            "apiVersion": "2015-05-01-preview"
        }
    ]
}

Values that you need to change in this request are:

  • name – name of your Azure SQL Managed Instance (don’t include domain).
  • properties/administratorLogin – SQL login that will be used to connect to the instance.
  • properties/subnetId – Azure identifier of the subnet where Azure SQL Managed Instance should be placed. Make sure that you properly
    configure network for Azure SQL Managed Instance. The easiest way to find this subnet id is to navigate to the desired subnet in the portal and copy this string from the Url in browser.
  • location – one of the valid location for Azure data centers, for example: “westcentralus”
  • sku/name: GP_Gen4 or GP_Gen5
  • properties/vCores: Number of cores that should be assigned to your instance. Values can be 8, 16, or 24 if you select GP_Gen4 sku name, or 8, 16, 24, 32, or 40 if you select GP_Gen5.
  • properties/storageSizeInGB: Maximum storage space for your instance. It should be multiple of 32GB.
  • properties/licenceType: Choose BasePrice if you don’t have SQL Server on-premises licence that you want to use, or LicenseIncluded if you can have discount for your on-premises licence.
  • tags(optional) – optionally put some key:value pairs that you would use to categorize instance.

Note that you cannot enter password as plain text – you need to specify parameters as a securestring, and pass it via PowerShell.

Once you create this JSON template you should save it to your local computer in some file (for example c:\\temp\newmi.json) and use this file as an input for PowerShell command that will execute it.

Invoking ARM template

In order to execute ARM template, you would need to install Azure RM PowerShell. In most of the cases the following three commands might install everything that you need:

Install-Module PowerShellGet -Force
Install-Module -Name AzureRM -AllowClobber
Install-Module -Name AzureRM.Sql -AllowPrerelease -Force

you would need at east 1.6.0 version of PowerShellGet. In some cases this version would not be loaded, so you would need to run something like:

Import-PackageProvider -Name PowerShellGet -Force -RequiredVersion 1.6.0

Then, you need to run something like to following PowerShell script:

Connect-AzureRmAccount

Select-AzureRmSubscription -Subscription "<put-your-subscription-id-here>"

$secpasswd = ConvertTo-SecureString "<put-strong-password>" -AsPlainText -Force

New-AzureRmResourceGroupDeployment -pwd $secpasswd -ResourceGroupName my_rg -TemplateFile 'c:\temp\newmi.json'

This script will first connect to your Azure account with Connect-AzureRmAccount where you will need to enter your Azure credential, select subscription where you want to put Managed Instance, create secure password, and execute New-AzureRmResourceGroupDeployment that will send ARM request to Azure API. -pwd parameter must match the name of the parameter in JSON ARM object and must be defined as secure string:

"parameters": {
    "pwd": { "type": "securestring" }
}

In this command you need to specify some resource group (my_rg in this example), and provide password and path to ARM JSON request file (c:\\temp\newmi.json in this case).

If there are no errors in your script, you will create new Managed Instance.