Working with SQL Managed Instance using Azure CLI

Jovan Popovic
Sep 5, 2018 · 5 min read

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.

Image for post
Image for post
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:

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:

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:

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:

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

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

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:

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:

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

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:

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.

Azure SQLDB Managed Instance

Stories about Azure SQL Database Managed Instance …

Jovan Popovic

Written by

Program manager working in Microsoft on Azure SQL Managed Instance and various SQL Server features such as T-SQL language, JSON, Hekaton, Column store, Temporal

Azure SQLDB Managed Instance

Stories about Azure SQL Database Managed Instance - Fully-managed SQL Server hosted in Azure cloud

Jovan Popovic

Written by

Program manager working in Microsoft on Azure SQL Managed Instance and various SQL Server features such as T-SQL language, JSON, Hekaton, Column store, Temporal

Azure SQLDB Managed Instance

Stories about Azure SQL Database Managed Instance - Fully-managed SQL Server hosted in Azure cloud

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store