Tag: Deploy Azure SQL Managed Instance with Powershell

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.