Creating Azure SQL Managed Instance using ARM templates

Article
05/15/2018

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.

Azure enables you describe resources that you need using ARM templates and specify in the code what kind of resources you need and what infrastructure you want to build (so called infrastructure as a code concept). If you are not familiar with ARM templates, it might be good to read how to create ARM template.

In order to create a new Azure SQL Managed Instance using ARM templates, 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": "https://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 want to bring your own SQL Server licence using AHB and to pay just for underlying infrastructure. If don't have SQL Server on-premises licence that you want to use, choose LicenseIncluded and SQL Server licence will be included in the price.
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.

Creating Azure SQL Managed Instance using ARM templates

Invoking ARM template

Additional resources