How to set up a managed SQL instance on Azure

An Azure SQL Managed Instance is Microsoft’s fully managed cloud database platform as a service (PaaS). This means there is no purchasing or maintaining any underlying infrastructure.

Create Azure SQL Managed Instance

To create an Azure SQL Managed Instance via the portal login to the Azure portal.

STEP 1: Select "Create a resource" on the left menu of the Azure portal. (If Azure SQL Managed Instance is not listed)
1 of 10

This demo will focus on creating an Azure Managed SQL Instance using the Azure Cli script below. We can use the below terminal to run the Azure Cli script to create the Azure Managed SQL Instance in our Azure Subscription.

Step 1: Login to Azure

  1. Login interactively using the Azure Cli in the terminal above using your valid azure account login credentials.
STEP 1 (1): Setup the terminal by setting the environment variables for the terminal
1 of 4
Terminal 1
Terminal
Loading...

Step 2: Create the Azure Managed SQL Instance

Copy and paste the Azure cli script into the terminal to create the Azure SQL Managed Instance.

# Create an Azure SQL Managed Instance
# Variable block
let "randomIdentifier=$RANDOM*$RANDOM"
location=$location
resourceGroup="eademo-azuresql-rg-$randomIdentifier"
tag="create-managed-instance"
vNet="eademo-azuresql-vnet-$randomIdentifier"
subnet="eademo-azuresql-subnet-$randomIdentifier"
nsg="eademo-azuresql-nsg-$randomIdentifier"
route="eademo-azuresql-route-$randomIdentifier"
instance="eademo-azuresql-instance-$randomIdentifier"
login="azureuser"
password="Pa$$w0rD-$randomIdentifier"
echo "Using resource group $resourceGroup with login: $login, password: $password..."
echo "Creating $resourceGroup in $location..."
az group create --name $resourceGroup --location "$location" --tags $tag 
echo "Creating $vNet with $subnet..."
az network vnet create --name $vNet --resource-group $resourceGroup --location "$location" --address-prefixes 10.0.0.0/16
az network vnet subnet create --name $subnet --resource-group $resourceGroup --vnet-name $vNet --address-prefixes 10.0.0.0/24 --delegations Microsoft.Sql/managedInstances
echo "Creating $nsg..."
az network nsg create --name $nsg --resource-group $resourceGroup --location "$location"
az network nsg rule create --name "allow_management_inbound" --nsg-name $nsg --priority 100 --resource-group $resourceGroup --access Allow --destination-address-prefixes 10.0.0.0/24 --destination-port-ranges 9000 9003 1438 1440 1452 --direction Inbound --protocol Tcp --source-address-prefixes "*" --source-port-ranges "*"
az network nsg rule create --name "allow_misubnet_inbound" --nsg-name $nsg --priority 200 --resource-group $resourceGroup --access Allow --destination-address-prefixes 10.0.0.0/24 --destination-port-ranges "*" --direction Inbound --protocol "*" --source-address-prefixes 10.0.0.0/24 --source-port-ranges "*"
az network nsg rule create --name "allow_health_probe_inbound" --nsg-name $nsg --priority 300 --resource-group $resourceGroup --access Allow --destination-address-prefixes 10.0.0.0/24 --destination-port-ranges "*" --direction Inbound --protocol "*" --source-address-prefixes AzureLoadBalancer --source-port-ranges "*"
az network nsg rule create --name "allow_management_outbound" --nsg-name $nsg --priority 1100 --resource-group $resourceGroup --access Allow --destination-address-prefixes AzureCloud --destination-port-ranges 443 12000 --direction Outbound --protocol Tcp --source-address-prefixes 10.0.0.0/24 --source-port-ranges "*"
az network nsg rule create --name "allow_misubnet_outbound" --nsg-name $nsg --priority 200 --resource-group $resourceGroup --access Allow --destination-address-prefixes 10.0.0.0/24 --destination-port-ranges "*" --direction Outbound --protocol "*" --source-address-prefixes 10.0.0.0/24 --source-port-ranges "*"
echo "Creating $route..."
az network route-table create --name $route --resource-group $resourceGroup --location "$location"
az network route-table route create --address-prefix 0.0.0.0/0 --name "primaryToMIManagementService" --next-hop-type Internet --resource-group $resourceGroup --route-table-name $route
az network route-table route create --address-prefix 10.0.0.0/24 --name "ToLocalClusterNode" --next-hop-type VnetLocal --resource-group $resourceGroup --route-table-name $route
echo "Configuring $subnet with $nsg and $route..."
az network vnet subnet update --name $subnet --network-security-group $nsg --route-table $route --vnet-name $vNet --resource-group $resourceGroup 
# This step will take awhile to complete. You can monitor deployment progress in the activity log within the Azure portal.
echo "Creating $instance with $vNet and $subnet..."
az sql mi create --admin-password $password --admin-user $login --name $instance --resource-group $resourceGroup --subnet $subnet --vnet-name $vNet --location "$location" --capacity 4 --storage 32GB --edition GeneralPurpose --family Gen5

Note: This step will take a while to complete. The deployment progress can be monitored in the activity log within the Azure portal.

1. Create Resource Group

Variable

Value

Description

randomIdentifier

$RANDOM*$RANDOM

This enables the function to add a random suffix.

location

$location

The user updates the azure region closest to their location.

resourceGroup

eademo-azuresql-rg-$randomIdentifier

The Resource Group is used to logically group all the Azure SQL Managed Instance.

tag

create-managed-instance

Tags help to logically organize azure resources. The tag values show up in cost reports and allow for other management activities by tag.

The table contains variables for the Azure CLI script to create a Resource Group to logically group all the Azure Managed SQL Instance. The variable values are used to create the resource group in azure with the azure cli command:

az group create --name $resourceGroup `
--location "$location" `
--tags $tag

2. Create Networking

Variable

Value

Description

vNet

eademo-azuresql-vnet-$randomIdentifier

A vNet is a Virtual Network, which creates a private network where the azure resources will be deployed.

subnet

eademo-azuresql-subnet-$randomIdentifier

A subnet is a sub section of virtual network where the managed SQL instance resources will be deployed.

nsg

eademo-azuresql-nsg-$randomIdentifier

An NSG is a Network Security Group. It acts as a filter for inbound and outbound traffic to your virtual network.

route

eademo-azuresql-route-$randomIdentifier

The route table supports directing traffic between subnets and networks.

2.1 Create virtual network with Subnet

The virtual network is the network were all the azure resources will live in Azure:

az network vnet create --name $vNet `
--resource-group $resourceGroup `
--location "$location" `
--address-prefixes 10.0.0.0/16

2.2 Create subnet

The subnet is a section of the virtual that contains a range of IP addresses. The resources that need an IP address will be allocated an IP Address from this range of numbers.

The subnet can also act as a security boundary. The Network security group and route tables will be attached to it later in the code 2.6.

az network vnet subnet create --name $subnet `
--resource-group $resourceGroup `
--vnet-name $vNet `
--address-prefixes 10.0.0.0/24 `
--delegations Microsoft.Sql/managedInstances

2.3 Create network security group

az network nsg create --name $nsg `
--resource-group $resourceGroup `
--location "$location"

2.4 Create network security group rules

The network security group rules control the inbound (ingress) and outbound (egress) traffic flow allowed on the subnet.

Inbound rules

az network nsg rule create --name "allow_management_inbound" `
--nsg-name $nsg --priority 100 `
--resource-group $resourceGroup `
--access Allow `
--destination-address-prefixes 10.0.0.0/24 ` 
--destination-port-ranges 9000 9003 1438 1440 1452 `
--direction Inbound `
--protocol Tcp `
--source-address-prefixes "*" `
--source-port-ranges "*"

az network nsg rule create --name "allow_misubnet_inbound"` 
--nsg-name $nsg `
--priority 200 `
--resource-group $resourceGroup `
--access Allow `
--destination-address-prefixes 10.0.0.0/24 `
--destination-port-ranges "*" `
--direction Inbound `
--protocol "*" `
--source-address-prefixes 10.0.0.0/24 `
--source-port-ranges "*"

az network nsg rule create --name "allow_health_probe_inbound" `
--nsg-name $nsg `
--priority 300 `
--resource-group $resourceGroup `
--access Allow `
--destination-address-prefixes 10.0.0.0/24 `
--destination-port-ranges "*" `
--direction Inbound `
--protocol "*" `
--source-address-prefixes AzureLoadBalancer `
--source-port-ranges "*"

Outbound rules

az network nsg rule create --name "allow_management_outbound" `
--nsg-name $nsg --priority 1100 `
--resource-group $resourceGroup `
--access Allow `
--destination-address-prefixes AzureCloud `
--destination-port-ranges 443 12000 `
--direction Outbound `
--protocol Tcp `
--source-address-prefixes 10.0.0.0/24 `
--source-port-ranges "*"

az network nsg rule create --name "allow_misubnet_outbound" `
--nsg-name $nsg `
--priority 200 `
--resource-group $resourceGroup `
--access Allow `
--destination-address-prefixes 10.0.0.0/24 `
--destination-port-ranges "*" `
--direction Outbound `
--protocol "*" `
--source-address-prefixes 10.0.0.0/24 `
--source-port-ranges "*"

2.5 Create network route table (UDR)

az network route-table create --name $route `
--resource-group $resourceGroup `
--location "$location"

az network route-table route create `
--address-prefix 0.0.0.0/0 `
--name "primaryToMIManagementService" `
--next-hop-type Internet `
--resource-group $resourceGroup `
--route-table-name $route

az network route-table route create `
--address-prefix 10.0.0.0/24 `
--name "ToLocalClusterNode" `
--next-hop-type VnetLocal `
--resource-group $resourceGroup --route-table-name $route

2.6 Attach network security group and route table to subnet

az network vnet subnet update --name $subnet `
--network-security-group $nsg `
--route-table $route `
--vnet-name $vNet `
--resource-group $resourceGroup

The following table contains variables for the Azure Cli script to create an Azure SQL Managed Instance using the script below:

3. Create Azure SQL Managed Instance in Subnet on vNet

Variable

Value

Description

instance

eademo-azuresql-instance-$randomIdentifier

This is the name of the azure sql managed instance.

login

azureuser

This is the sql username to login to the sql instance.

password

Pa$$w0rD-$randomIdentifier

This is the password to login to the sql managed instance.

The variable values in the table are used to create the SQL Managed Instance in the code below:

az sql mi create --admin-password $password `
--admin-user $login `
--name $instance `
--resource-group $resourceGroup `
--subnet $subnet `
--vnet-name $vNet `
--location "$location"

NOTE: Don’t forget to delete the created resources in Azure.

az group delete -n $ResourceGroup
widget

Free AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
You TubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

License: Creative Commons-Attribution NonCommercial-ShareAlike 4.0 (CC-BY-NC-SA 4.0)