{ "metadata": { "kernelspec": { "name": "powershell", "display_name": "PowerShell" }, "language_info": { "name": "powershell", "codemirror_mode": "shell", "mimetype": "text/x-sh", "file_extension": ".ps1" } }, "nbformat_minor": 2, "nbformat": 4, "cells": [ { "cell_type": "markdown", "source": [ "# Create Azure SQL Managed Instance\n", "\n", "## Description\n", "\n", "This PowerShell script example creates a managed instance in a dedicated subnet within a new virtual network. It also configures a route table and a network security group for the virtual network. Once the script has been successfully run, the managed instance can be accessed from within the virtual network or from an on-premises environment. See [Configure Azure VM to connect to Azure SQL Database Managed Instance](https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/connect-vm-instance-configure) and [Configure a point-to-site connection to Azure SQL Managed Instance from on-premises](https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/point-to-site-p2s-configure).\n", "\n", "" ], "metadata": { "azdata_cell_guid": "2dcca704-2945-4763-8cbe-116b90696f9c" } }, { "cell_type": "markdown", "source": [ "Steps of this procedure include:\r\n", "1. Connect to Azure subscription\r\n", "1. Provision resource group for SQL Managed Instance\r\n", "2. Configure Network Settings\r\n", "3. Create credentials to be used for Managed Instance\r\n", "4. Create Managed Instance" ], "metadata": { "azdata_cell_guid": "dd1cfc56-2919-416b-968d-cdf3345dcb1c" } }, { "cell_type": "code", "source": [ "$NSnetworkModels = \"Microsoft.Azure.Commands.Network.Models\"\r\n", "$NScollections = \"System.Collections.Generic\"\r\n", "\r\n", "# Set the resource group name and Location for your managed instance\r\n", "$ResourceGroupName = \"myResourceGroup-$(Get-Random)\"\r\n", "$Location = \"eastus2\"\r\n", "\r\n", "# Set the networking values for your managed instance\r\n", "$VNetName = \"myVnet-$(Get-Random)\"\r\n", "$VNetAddressPrefix = \"10.0.0.0/16\"\r\n", "$DefaultSubnetName = \"myDefaultSubnet-$(Get-Random)\"\r\n", "$DefaultSubnetAddressPrefix = \"10.0.0.0/24\"\r\n", "$MiSubnetName = \"myMISubnet-$(Get-Random)\"\r\n", "$MiSubnetAddressPrefix = \"10.0.0.0/24\"\r\n", "\r\n", "#Set the managed instance name for the new managed instance\r\n", "$InstanceName = \"myMIName-$(Get-Random)\"\r\n", "# Set the admin login and password for your managed instance\r\n", "$MiAdminSqlLogin = \"SqlAdmin\"\r\n", "$MiAdminSqlPassword = \"ChangeYourAdminPassword1\"\r\n", "\r\n", "# Set the managed instance service tier, compute level, and License mode\r\n", "$Edition = \"General Purpose\"\r\n", "$VCores = 8\r\n", "$MaxStorage = 256\r\n", "$ComputeGeneration = \"Gen5\"\r\n", "$License = \"LicenseIncluded\" #\"BasePrice\" or LicenseIncluded if you have don't have SQL Server licence that can be used for AHB discount\r\n", "" ], "metadata": { "azdata_cell_guid": "e574921c-3611-47d7-a872-564d07030815" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Connect to Azure Account" ], "metadata": { "azdata_cell_guid": "2ab3812a-cb4f-44a1-94d4-a0681a05606f" } }, { "cell_type": "code", "source": [ "Connect-AzAccount" ], "metadata": { "azdata_cell_guid": "b137afd5-512a-4261-90c0-b43dbf1724ff" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Get Subscription\r\n", "Below command will open a Dialouge Box with list of subscriptions.\r\n", "Selecting one of those will set that subscription for rest of the commands." ], "metadata": { "azdata_cell_guid": "d3af080f-c3d1-4a04-a168-78f8bfce247b" } }, { "cell_type": "code", "source": [ "$subscription = Get-AzSubscription | Out-GridView -PassThru\r\n", "Set-AzContext -SubscriptionName $subscription" ], "metadata": { "azdata_cell_guid": "6356291d-96e4-4cbb-bae8-27e81f6e0bb8" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Create the resource group\r\n", "Create a resource group with the _New-AzResourceGroup_ command. An Azure resource group is a logical container into which Azure resources are deployed and managed. A resource group must be created first:" ], "metadata": { "azdata_cell_guid": "c8a4555b-8b80-41f9-9e8f-eaab3b419030" } }, { "cell_type": "code", "source": [ "# Create Azure resource group, if necessary\r\n", "$resourceGroup = Get-AzResourceGroup -Name $ResourceGroupName\r\n", "\r\n", "if (!$resourceGroup)\r\n", "{\r\n", " # Need to create a new resource group\r\n", " Write-Output \"Resource Group $RG1 does not exist. Creating...\"\r\n", " $resourceGroup = New-AzResourceGroup -Name $RG1 -Location $Location -Tag @{Owner=\"SQLDB-Samples\"}\r\n", "}" ], "metadata": { "azdata_cell_guid": "920c904f-6c6b-4e59-8305-f34b44297a64" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Configure virtual network, subnets, network security group, and routing table" ], "metadata": { "azdata_cell_guid": "2ed3ccec-ab94-4c20-aa74-6d67c3db014d" } }, { "cell_type": "code", "source": [ "$networkSecurityGroupMiManagementService = New-AzNetworkSecurityGroup `\r\n", " -Name 'myNetworkSecurityGroupMiManagementService' `\r\n", " -ResourceGroupName $ResourceGroupName `\r\n", " -Location $Location\r\n", "\r\n", "$routeTableMiManagementService = New-AzRouteTable `\r\n", " -Name 'myRouteTableMiManagementService' `\r\n", " -ResourceGroupName $ResourceGroupName `\r\n", " -Location $Location\r\n", "\r\n", "$virtualNetwork = New-AzVirtualNetwork `\r\n", " -ResourceGroupName $ResourceGroupName `\r\n", " -Location $Location `\r\n", " -Name $VNetName `\r\n", " -AddressPrefix $VNetAddressPrefix\r\n", "\r\n", " Add-AzVirtualNetworkSubnetConfig `\r\n", " -Name $MiSubnetName `\r\n", " -VirtualNetwork $virtualNetwork `\r\n", " -AddressPrefix $MiSubnetAddressPrefix `\r\n", " -NetworkSecurityGroup $networkSecurityGroupMiManagementService `\r\n", " -RouteTable $routeTableMiManagementService |\r\n", " Set-AzVirtualNetwork\r\n", "\r\n", "$virtualNetwork = Get-AzVirtualNetwork -Name $VNetName -ResourceGroupName $ResourceGroupName\r\n", "\r\n", "$subnet= $virtualNetwork.Subnets[0]" ], "metadata": { "azdata_cell_guid": "1f7cc6ec-ef04-4ffe-85d5-6ec40c5b2d1c" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Create a delegation" ], "metadata": { "azdata_cell_guid": "a55da861-b130-451f-a017-4a033123e76d" } }, { "cell_type": "code", "source": [ "$subnet.Delegations = New-Object \"$NScollections.List``1[$NSnetworkModels.PSDelegation]\"\r\n", "$delegationName = \"dgManagedInstance\" + (Get-Random -Maximum 1000)\r\n", "$delegation = New-AzDelegation -Name $delegationName -ServiceName \"Microsoft.Sql/managedInstances\"\r\n", "$subnet.Delegations.Add($delegation)\r\n", "\r\n", "Set-AzVirtualNetwork -VirtualNetwork $virtualNetwork\r\n", "\r\n", "$miSubnetConfigId = $subnet.Id\r\n", "\r\n", "\r\n", "\r\n", "$allowParameters = @{\r\n", " Access = 'Allow'\r\n", " Protocol = 'Tcp'\r\n", " Direction= 'Inbound'\r\n", " SourcePortRange = '*'\r\n", " SourceAddressPrefix = 'VirtualNetwork'\r\n", " DestinationAddressPrefix = '*'\r\n", "}\r\n", "$denyInParameters = @{\r\n", " Access = 'Deny'\r\n", " Protocol = '*'\r\n", " Direction = 'Inbound'\r\n", " SourcePortRange = '*'\r\n", " SourceAddressPrefix = '*'\r\n", " DestinationPortRange = '*'\r\n", " DestinationAddressPrefix = '*'\r\n", "}\r\n", "$denyOutParameters = @{\r\n", " Access = 'Deny'\r\n", " Protocol = '*'\r\n", " Direction = 'Outbound'\r\n", " SourcePortRange = '*'\r\n", " SourceAddressPrefix = '*'\r\n", " DestinationPortRange = '*'\r\n", " DestinationAddressPrefix = '*'\r\n", "}\r\n", "\r\n", "Get-AzNetworkSecurityGroup `\r\n", " -ResourceGroupName $ResourceGroupName `\r\n", " -Name \"myNetworkSecurityGroupMiManagementService\" |\r\n", " Add-AzNetworkSecurityRuleConfig `\r\n", " @allowParameters `\r\n", " -Priority 1000 `\r\n", " -Name \"allow_tds_inbound\" `\r\n", " -DestinationPortRange 1433 |\r\n", " Add-AzNetworkSecurityRuleConfig `\r\n", " @allowParameters `\r\n", " -Priority 1100 `\r\n", " -Name \"allow_redirect_inbound\" `\r\n", " -DestinationPortRange 11000-11999 |\r\n", " Add-AzNetworkSecurityRuleConfig `\r\n", " @denyInParameters `\r\n", " -Priority 4096 `\r\n", " -Name \"deny_all_inbound\" |\r\n", " Add-AzNetworkSecurityRuleConfig `\r\n", " @denyOutParameters `\r\n", " -Priority 4096 `\r\n", " -Name \"deny_all_outbound\" |\r\n", " Set-AzNetworkSecurityGroup" ], "metadata": { "azdata_cell_guid": "cb4292f6-1a78-43ec-81cb-505ebd791527" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Create credentials" ], "metadata": { "azdata_cell_guid": "e83be748-e6ec-46e1-b056-18be20185195" } }, { "cell_type": "code", "source": [ "$secpassword = ConvertTo-SecureString $MiAdminSqlPassword -AsPlainText -Force\r\n", "$credential = New-Object System.Management.Automation.PSCredential ($MiAdminSqlLogin, $secpassword)\r\n", "" ], "metadata": { "azdata_cell_guid": "63bca2ea-fbf8-4e2d-8f58-a2b7bd913f6e" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Create managed instance" ], "metadata": { "azdata_cell_guid": "d17b701e-4c36-423c-b297-602879022621" } }, { "cell_type": "markdown", "source": [ "### This script will take a minimum of 3 hours to create a new managed instance in a new virtual network. \r\n", "### A second managed instance is created much faster." ], "metadata": { "azdata_cell_guid": "3fdb03c6-3404-4ab6-9795-c2293cbea693" } }, { "cell_type": "code", "source": [ "New-AzSqlInstance -Name $InstanceName `\r\n", " -ResourceGroupName $ResourceGroupName -Location $Location -SubnetId $miSubnetConfigId `\r\n", " -AdministratorCredential $credential `\r\n", " -StorageSizeInGB $MaxStorage -VCore $VCores -Edition $Edition `\r\n", " -ComputeGeneration $ComputeGeneration -LicenseType $License\r\n", "" ], "metadata": { "azdata_cell_guid": "e3b55f89-d795-4d98-a24d-ade53cf53648" }, "outputs": [], "execution_count": null } ] }