Analysing inventory of Managed Instances using T-SQL

Sometime you would need to know how many Managed Instance you have created in Azure cloud. Although you can find all information about the Azure SQL Managed Instances in Azure portal or API (ARM, PowerShell, Azure CLI), sometime it is hard to list all instances and search them using some criteria. In this post you will see how easily you can load list of your Managed Instances and build inventory of your resources.

Problem

Imagine that you have a large number of Managed Instances and you need to know how many instances you have, in what regions, subnets, and virtual networks they are placed, how much compute and storage is allocated to each of them, etc. Analyzing inventory of Managed Instances might be hard if you just use PowerShell.

Solution

Azure ARM templates enable you to get a list of your managed instances with all necessary properties as JSON object. All you need to to is to load this JSON object in your database table and analyze it sing standard T-SQL language.

As an example, you can list all of your Managed Instances using Azure CLI:

 az login

az account set --subscription a7c7b824-xxxx-xxxx-xxxx-e6b827082m1a

az sql mi list >> mi.txt

This command will return a list of your managed instances within the subscription as one JSON array stored in the file mi.txt.

Now you need to get the content of mi.txt, connect to some Azure SQL Database, Managed Instance, or SQL Server 2016 or higher where you have JSON support and use OPENJSON function to load this JSON in a table or query it.

An example of a query that reads data from JSON is shown in the following code:

 declare @json nvarchar(max) = N'<<put the content of mi.txt here>>';

select *,
   vNet = SUBSTRING(subnetId, 
         PATINDEX('%/virtualNetworks/%', subnetId) + 17, 
                        PATINDEX('%/subnets/%', subnetId) - PATINDEX('%/virtualNetworks/%', subnetId) - 17),
    subnet = SUBSTRING(subnetId, 
           PATINDEX('%/subnets/%', subnetId) + 9, 
                        200),
    [Number of instances in this subnet] = count(*) over (partition by subnetId)
from openjson(@json)
with (name nvarchar(400), storageSizeInGb int, vCores tinyint,
      location varchar(30), subnetId nvarchar(4000),
      tier varchar(20) '$.sku.tier', hardware varchar(8) '$.sku.family', licenseType varchar(20), resourceGroup varchar(100), state varchar(20)
       )

This query will return all information about your Managed Instances that can be loaded into a table and analyzed using standard SQL language.