Working with SQL Managed Instance using Azure CLI

Azure SQL Managed Instance is a fully managed SQL Server Database engine deployed as PaaS service in Azure cloud.

One of the main benefits of PaaS and hosting in the cloud is that you don’t need to worry about installation and setup of SQL Server. If you need a new fully-managed SQL Server instance, you can just go to the Azure portal or type few commands in the command line and you will have instance ready to run. Also, if you don’t need the instance anymore, you can easily delete it without worrying about underlying hardware.

Azure Command Line Interface (CLI) is a command line library that enables you to quickly and easily work with your Azure SQL Managed Instances. In this article, you will see some basic CLI commands that can be used with Managed Instance.

You can run Azure CLI commands using Azure portal, Azure Cloud Shell(using shell.azure.com), Azure mobile app, your command prompt on your local computed using Azure CLI 2.0, or VS Code Azure Account extension.

If you want to quickly try it without some installation, you just need to open shell.azure.com site where you can type the commands online. Otherwise, you will need to download and install Azure CLI locally. In the rest of this story I will use Azure online shell command line.

Azure cloud shell

All Azure CLI commands that work with SQL Managed Instances start with az sql mi where you can add the specific commands (create, update, delete, list and show) and the parameters specific for the selected command.

Prerequisite

The prerequisite for this sample is to prepare Azure resource group , Virtual Network and subnet where the Managed Instance will be created using the instructions described here. The easiest way to setup environment is to use ARM template deployment as described in this post.

Make sure that you set the subscription that you will use using something like the following command:

az account set --subscription b9c7a824-4bde-06c0-9778-e7c2a70573e1

Replace the value b9c7a824-4bde-06c0-9778-e7c2a70573e1 with your subscription id.

Working with Managed Instances

You can list all Managed Instances in your subscription using the following command:

az sql mi list

This command will show you all sql managed instances in your subscription in one big JSON document. Currently, you can filter your search only by specifying azure resource group where you want to search for the instances using the -g my_rg_name option. If you want more advanced filtering, you can specify JMESPath query string in --query parameter.

The command az sql mi create will create a new Managed Instance:

az sql mi create -n jovanpop-temp-mi -u myMiAdmin -p Stron9Pwd1234
           -g mi_group -l "West Central US"
           --vnet-name mi_vnet --subnet mi_subnet

In this command you need to specify the name of new managed instance (-n), admin username(-u) and password(-p), resource group where it should be placed (-g), location(data center) where the instance will be placed (-l), and VNet/subnet where the instance will be configured.

If the command succeeds, you will see the properties of the created Managed Instance in the output as JSON text. Note that newly created Managed Instance will not be shown in the Azure deployments.

You can also specify the following properties of new Managed Instance in this command:

• -c number of cores that will be assigned to the instance.
• --storage storage size expressed in GB
• --license-type– that can be BasePrice or LicenseIncluded
• --tier– GeneralPurpose or BusinessCritical
• --family– hardware family that can be Gen4 or Gen

A command that specifies all these properties is shown in the following example:

az sql mi create -n jovanpop-temp-mi -u myMiAdmin -p StrongPwd1234
            -g mi_group
            -l "West Central US"
            --vnet-name mi_vnet --subnet mi_subnet
            -c 8 --storage 256 --tier BusinessCritical
            -license-type LicenseIncluded --family Gen4

You can also retrieve properties of the instance using the show command and specifying name and resource group:

az sql mi show -n jovanpop-temp-mi -g mi_group

If you want to change the properties of some instance you can use az sql mi update command:

az sql mi update -g mi_group -n jovanpop-temp-mi --storage 64

This command might be very useful if you need to quickly scale up or scale down the instance by changing the assigned storage size limit or number of cores.

Finally, you can delete the instance that you don’t need anymore using az sql mi delete command:

az sql mi delete -g mi_group -n jovanpop-temp-mi

Working with databases

Although the primary usage of Azure CLI would be to create and update your Managed Instances, CLI also enables you to work with the databases on your instance using az sql midb commands.

The following three commands list all databases on your instance, create and delete new database:

az sql midb list -g mi_group --mi jovanpop-temp-mi
az sql midb create -g mi_group --mi jovanpop-temp-mi --name NewDb
az sql midb delete -g mi_group --mi jovanpop-temp-mi -n NewDb

Probably the most common administrative database operation that you will do with Azure CLI is database restore using az sql midb restore command.

az sql midb restore -g mi_group --mi jovanpop-temp-mi -n ExistingDb --time "2018-05-20T05:34:22" --dest-name NewRestoredDb

In this command you should specify resource group (-g), name of the managed instance (--mi), name of the existing database (--dest-name) that has backups, time in the past when the snapshot should be taken (--time), and name of the new database (--dest-name) that will represent point-in-time snapshot of the existing database.

Note that restore command will create a new database as a copy of the existing one. You cannot overwrite the existing database with some previous copy of this database.

Azure CLI might be handy when you migrate/copy databases from one Managed Instance to another. Since every database has automated backups, you can restore the latest backup of a database from one instance into another destination instance:

az sql midb restore -g migroup
                    --mi jovanpop-source-mi -n OriginalDatabaseName
                    --dest-mi jovanpop-target-mi
                    --dest-name NewDatabaseName

The parameters that you need to provide are:

• -g representing Azure resource group where source and destination managed instances are placed (destination resource group can be changed)
• –mi representing the name of managed instance where source database is placed
• –n that represents a name of the database that you want to restore on the another instance
• --dest-mi representing the name of managed instance
• --dest-name that represents a name of the database that you want to restore on another instance

Beware that this is the copy of the database taken from the last backup, so it might lag a few minutes behind original. If you want to use this approach to copy data, make sure that you stop any activity on the source database to make sure that the last changes are backed-up.

You can also restore a copy of database from some point in time in the past by providing --time parameter.

Currently, you could restore database to another instance within the same subscription and region. Cross-region restores are still not supported.

You can find additional parameters (such as different destination resource group) on az sql midb restore documentation page.

Summary

One you learn Azure CLI it might became handy way to control your resources and change the properties of your managed instances. You can find complete list of AZ commands that can be used to control managed instance in Azure documentation page.