How-to find out the SQL MI Service Tier basic details with T-SQL

We are starting a series of blog posts on how to find out the necessary information about Azure SQL Managed Instance (SQL MI) for your automation scripts and tools. We hope it will help you to build some extraordinary applications on the product that we love building.

There are already known ways to determine these properties through the Azure Portal or through your favorite script language (Powershell, Azure CLI or any other), we just wanted to provide some useful information on the good, old, original and ever-useful T-SQL.

You can find and reference the whole series with the following link http://aka.ms/sqlmi-howto.

Even though Azure SQL Managed Instance in its origin is the same as SQL Server, there are some specifics about SQL MI that cannot be found on the on-premises or Infrastructure as a Service (IaaS) offerings of SQL Server. Those differences start with the Service Tier that is a part of the Platform as a Service (PaaS) offering. 

So here are some of the basic how-tos: 

How-to ensure that Managed Instance is your engine

This information and recommendation have been public since the launch of the Azure SQL Managed Instance - the value that the SERVERPROPERTY function returns, specific to the SQL MI has a value of 8.

IF( SERVERPROPERTY('EngineEdition') = 8 ) 
BEGIN
    PRINT 'This is an Azure SQL Managed Instance.';
END
ELSE
BEGIN
	PRINT 'This is NOT an Azure SQL Managed Instance.';
END

On the image provided, you can see the output, if you are running your code on Azure SQL Managed Instance.

How-to find out the Service Tier of your Managed Instance

Right now there are 2 offerings - GP (General Purpose) & BC (Business Critical), each one focusing on the specific type of the workloads with specific requirements, but there is no guarantee that one day it might not suffer changes, and any of the alternative ways of finding it out are risky, and so here is the official way of find out through the sys.server_resource_stats DMV, that provides a good amount of useful  and important information, regarding the Managed Instance service tier.

The Azure SQL MI Platform as a Service (PaaS) offering is a very dynamic and potentially a tier can be changed by the user at almost any moment with just a couple of clicks or a single T-SQL batch, making it quite different to a regular SQL Server installation, where things are less likely to change with the same ease, typically.

In order to obtain the value of the current SQL MI Service Tier, please get the latest available value for the SKU from the above mentioned sys.server_resource_stats view:

SELECT TOP 1 sku as ServiceTier
	FROM [sys].[server_resource_stats]
	ORDER BY end_time DESC;

In my case, I have executed this query on the Generic Purpose instance, as you ca see in the results pane. You can easily save this value into a variable or use it directly in an IF statement to build some logic around your code.

How-to find out used Hardware Generation

It is important to know the generation of the hardware that is being used on your Azure SQL Managed Instance. Besides knowing wether you are running the latest and the greatest generation of hardware available, some of the constraints (such as RAM limits) have been connected to it, and understanding and checking this information might be important for troubleshooting and/or optimization.

Currently, only Generation 5 (Gen 5) is available for your SQL MI, but at the launch of SQL MI, a couple of years ago, we had 2 different generations available - including now obsolete Generation 4 (Gen 4), and one day in the future, there might be others. 

To check this information, we shall be using the sys.server_resource_stats DMV again, this time selecting the [hardware_generation] column as the result:

SELECT TOP 1 hardware_generation as HardwareGeneration
	FROM [sys].[server_resource_stats]
	ORDER BY end_time DESC;

In this blog post we have provided you with just a couple of T-SQL scripts that will allow your better discovery of the SQL MI environment with the help of T-SQL. Stay tuned for even more insight that we are looking to bring to you.