Part 1 of this series, went over the steps on how to create a SQL Managed Instance using the Azure Portal. In part 2, I described using a PowerShell script to create a Managed Instance. For this final part in deploying SQL Managed Instances, I will go over what it would take to use an ARM Template.
To start off, I used this blog post as a starting point and plan on building it from there.
A SQL Managed Instance has some requirements that were discussed in part 2. The article goes into a little more detail on how to get the requirements setup. This article from Microsoft goes into how to configure a VNet for a Managed Instance.
As a review here are the prerequisites for a SQL Managed Instance in Azure.
- Setup/configure a VNet (Virtual Network) and a subnet. Once a VNet is created, you will need to create a dedicated subnet that the Managed Instance will be in, please note the subnet cannot contain any other cloud service.
- A compatible Network Security Group (NSG), the site mentioned above has the security rules laid out in detail.
- Create a user route table with 0.0.0.0/0 Next Hop Internet as the mandatory user-defined route table (UDR) assigned to it.
- Must not have a service endpoint associated with it.
- Must have a minimum of 16 IP addresses assigned to it.
Once all the requirements are met, you will need to obtain the Subnet ID, it should look something like the following. We will be using this in the Parameters file for the ARM Template.
/subscriptions/<Subscription ID>/resourceGroups/RG-DBGRL93-01P/providers/Microsoft.
Network/virtualNetworks/vnet-dba-mi-azcloud/subnets/snet-dba-mi-azcloud
The first part of the ARM Template will be the parameters. This, really is not too different from the parameters section of the Azure SQL Database template we used. As you can see, I use a lot of the same parameters.
"parameters": {
"administratorLogin": {
"type": "string"
},
"administratorLoginPassword": {
"type": "securestring"
},
"location": {
"type": "string"
},
"managedInstanceName": {
"type": "string"
},
"subnetId": {
"type": "string"
},
"skuName": {
"type": "string",
"defaultValue": "GP_Gen4"
},
"skuEdition": {
"type": "string",
"defaultValue": "GeneralPurpose"
},
"storageSizeInGB": {
"type": "int",
"defaultValue": "32"
},
"vCores": {
"type": "int",
"defaultValue": "16"
},
"licenseType": {
"type": "string",
"defaultValue": "LicenseIncluded"
},
"hardwareFamily": {
"type": "string",
"defaultValue": "Gen4"
}
},
In this example, I am using a separate Parameters file. This is where I tell it the subnet ID I created and to use it for the Managed Instance.
"subnetId": {
"value": "/subscriptions//resourceGroups/RG-DBGRL93-01P/providers/Microsoft.Network/virtualNetworks/vnet-dba-mi-azcloud/subnets/snet-dba-mi-azcloud"
},
Usually after the Parameters section, you would create any variables that may be needed for the template. In this example, I am not going to be using any variables. I will be going straight to the resources section.
As you can see, it is pretty straight forward. If you would like, there is a resources template for adding databases to the managed instance. In this post, I am keeping it pretty basic and just deploying the Managed Instance.
"resources": [
{
"apiVersion": "2015-05-01-preview",
"type": "Microsoft.Sql/managedInstances",
"identity": {
"type": "SystemAssigned"
},
"location": "[parameters('location')]",
"name": "[parameters('managedInstanceName')]",
"sku": {
"name": "[parameters('skuName')]",
"tier": "[parameters('skuEdition')]"
},
"properties": {
"administratorLogin": "[parameters('administratorLogin')]",
"administratorLoginPassword": "[parameters('administratorLoginPassword')]",
"subnetId": "[parameters('subnetId')]",
"storageSizeInGB": "[parameters('storageSizeInGB')]",
"vCores": "[parameters('vCores')]",
"licenseType": "[parameters('licenseType')]",
"hardwareFamily": "[parameters('hardwareFamily')]"
}
}
Finally, to deploy the Managed Instance using Powershell.
New-AzureRmResourceGroupDeployment -administratorLoginPassword $secpasswd -ResourceGroupName 'RG-DBGRL93-01P' -TemplateFile 'C:\DBAInTheCloud2\ARMTemplates\DBA_ARMTemplt_SQLMI.json' -TemplateParameterFile 'C:\DBAInTheCloud2\ARMTemplates\DBA_ARMParams_SQLMI.json'
When deploying a Managed Instance for the first time in a VNet, I have seen it take up to 3 hours. Once it is complete, you will be able to complete the configurations and connect to it using SQL Server Management Studio.
Hope this helps in deploying SQL Managed Instance.