Tag: Azure SQL Managed Instance

How To Deploy an Azure SQL Managed Instance using ARM Templates – Part 3

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.

  1.  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.
  2. A compatible Network Security Group (NSG), the site mentioned above has the security rules laid out in detail.
  3. 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.
  4. Must not have a service endpoint associated with it.
  5. 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.

How to Deploy an Azure SQL Managed Instance Using PowerShell – Part 2

In Part 1 of this series, I talked a little about what a SQL Managed Instance is and how it works like an on premise SQL Server Instance.  In this next part of the series, I will be showing the steps necessary to deploy a Managed Instance using PowerShell.

Deploying a SQL Managed Instance is a little different than that of a SQL Database in Azure or even a SQL Server instance on a virtual machine.  There are a couple of prerequisites for a SQL Managed Instance.

Requirements

You can find more detail from Microsoft in the article titled Configure a VNet for Azure SQL Database Managed Instance

  1.  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 could service.
  2. A compatible Network Security Group (NSG), the site mentioned above has the security rules laid out in detail.
  3. 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.
  4. Must not have a service endpoint associated with it.
  5. Must have a minimum of 16 IP addresses assigned to it.

Here is a sample script to setup a VNet and a subnet.  Here, I am setting up the parameters for the names of the VNet and Subnet.  I can then create the VNet and the SubNet using the PowerShell commands.


$ResourceGroupName = "RG-DBGRL93-01P"
$VNetName = "vnet-dba-mi-azcloud"
$SubNetName = "snet-dba-mi-azcloud"

$virtualNetwork = New-AzureRmVirtualNetwork `
  -ResourceGroupName $ResourceGroupName `
  -Location "North Central US" `
  -Name $VNetName `
  -AddressPrefix 10.0.0.0/16

  $subnetConfig = Add-AzureRmVirtualNetworkSubnetConfig `
  -Name $SubNetName `
  -AddressPrefix 10.0.0.0/24 `
  -VirtualNetwork $virtualNetwork

  $virtualNetwork | Set-AzureRmVirtualNetwork

To Create the Route Table and Route


$Route = New-AzureRmRouteConfig -Name "dbatc-mi-rte" -AddressPrefix 0.0.0.0/0 -NextHopType "Internet"
New-AzureRmRouteTable -Name "RouteTableMI" -ResourceGroupName $ResourceGroupName -Location "North Central US" -Route $Route

Once the VNet, Subnet and Route Table are in place you are ready to deploy the SQL Managed Instance.

I found the following script here, it is a pretty good article showing that has another example using PowerShell.  In this snippet, you can see that the command to create the SQL Managed Instance needs the Subnet ID.  We can get that by running the Get-AzureRmVirtualNetwork and the Get-AzureRmVirtualNetworkSubnetConfig commands.

The Subnet ID needs to be in the format of, getting this ID will be necessary for the ARM Template script I will be working on for the next article.

/subscriptions/<Subscription ID>/resourceGroups/RG-DBGRL93-01P/providers/Microsoft.
Network/virtualNetworks/vnet-dba-mi-azcloud/subnets/snet-dba-mi-azcloud

Now, once we create the requirements, we can setup PowerShell to run the commands to deploy the managed instance.  In this script, I am using the command New-AzureRmSqlInstance.  This command is available after installing the AzureRM.SQL module

Install-Module -Name AzureRM.Sql -RequiredVersion 4.12.1
Then run the
Import-Module AzureRm.Sql

Once that is setup you can put it all together as below.


$ResourceGroupName = "RG-DBGRL93-01P"
$VNetName = "vnet-dba-mi-azcloud"
$SubNetName = "snet-dba-mi-azcloud"

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


New-AzureRmSqlInstance -Name "dbaitc-mi-ps-01" `
-ResourceGroupName $ResourceGroupName -Location "North Central US" -SubnetId $subnetId `
-AdministratorCredential (Get-Credential) `
-StorageSizeInGB 1024 -VCore 8 -Edition "GeneralPurpose" `
-ComputeGeneration Gen4 -LicenseType LicenseIncluded

Hope this helps out, since Managed Instances are still pretty new, it took me some time to figure this out.  Go ahead and give it a try and let me know how it goes.