In this post I want to cover spreading your SQL Server wings with Azure SQL Managed Instances (aka Managed Instances). As part of a series of posts about spreading your SQL Server wings with the Microsoft Intelligent Data Platform.
You can find out more about this series of posts in an introductory post I published about the series.
By the end of this post, you will have a good overview of Azure SQL Managed Instance. In addition, how your SQL Server background can come in handy with this service. Along the way I share plenty of links as well.
Since I previously covered spreading your SQL Server wings with Azure SQL Database I thought it was only fair I covered the other Azure SQL Platform as a Service (PaaS) offerings as well.
To help with some jargon here, Azure SQL refers to the collection of the below Azure SQL database services that are available in Azure:
- SQL Server on Azure Virtual Machines
- Azure SQL Database
- Azure SQL Edge
- Azure SQL Managed Instance (aka Managed Instances)
Microsoft provides a dedicated experience in the portal which allows you to deploy any of the above members of the Azure SQL family. Which you can see below.
You can find out more about what I mean when I refer to Platform as a Service (PaaS) in an old blog post called ‘SaaS, PaaS, and IaaS.. Oh my!’.
Azure SQL Managed Instances
Azure SQL Managed Instance (aka Managed Instance) is a Platform as a Service solution that deploys an entire instance to SQL Server. With some variations due to how it was developed.
Which I think is great. Because you get most of the features that you get with an on-premises install of SQL Server. Including SQL Server Agent.
I think a lot of people in the community were pleased when this service was finally made available. Due to some limitations with Azure SQL Database. Including some relating to migration support and security.
Before I go any further, I want to point out that there are two different ways that you can install Azure SQL Managed Instances. Either directly as a Platform as a Service (PaaS) or as an Azure Arc-enabled data service. As you can see below.
I focus more on the offering that is available when you deploy directly to Azure in this post. However, I wanted to make sure you were aware that Arc-enabled SQL Managed Instances are also an option.
It can take a while to deploy an Azure SQL Managed Instance in Azure. Back in 2019 the fastest I was able to deploy was three hours and forty-two minutes.
However, it look like deployment times have now improved. With the same settings I can now deploy a new Manage Instance in two hours and forty-eight minutes. Feel free to share your fastest deployment time.
To manage expectations here, I also want to stress the more Azure knowledge you have the easier it will be to work with Azure SQL Managed Instances. Especially as far as network connectivity and authentication are concerned.
Similarities and differences with Azure SQL Database
Since I covered spreading your SQL Server wings with Azure SQL Database in a previous post, I thought I should point out some similarities and differences.
Main difference is that Azure SQL Database focuses on a single database whereas an Azure SQL Managed Instance focuses more on an entire instance.
Just like with Azure SQL Database, you can deploy Azure SQL Managed Instances with different service tiers.
In addition, you can manage Azure SQL Managed Instances using various applications as well. Including SQL Server Management Studio. Which I have expanded below to highlight the fact that Azure SQL Managed Instances supports more. Including SQL Server Agent.
In reality, there are a lot of feature differences between Azure SQL Database and Azure SQL Managed Instances. Microsoft provides a full feature comparison between the two.
If you view the feature comparison you can see that there are many ways that you can utilize your SQL Server background within Azure SQL Managed Instances. Including with replication and SQL Server Agent.
In addition, you can utilize a lot of your T-SQL knowledge as well. For example, the current default compatibility level for new databases is SQL Server 2019. However, you can change this to various versions including SQL Server 2022 using T-SQL.
However, be aware that there are a number of differences between the T-SQL support in SQL Server and Azure SQL Managed Instances. Microsoft provides a guide that explains the T-SQL differences between SQL Server & Azure SQL Managed Instance.
CI/CD for Azure SQL Managed Instances
A bit of advice if you want to perform CI/CD on databases within Azure SQL Managed Instances.
I recommend checking the compatibility level of the database you want to deploy to in Azure SQL Managed Instance.
As I mentioned earlier, the default compatibility level for new databases is SQL Server 2019. So, if you are deploying database updates by using a dacpac you can keep the same target platform if you change from deploying to SQL Server 2019 to deploying to an Azure SQL Managed Instance.
This can be useful in various scenarios. For example, if you are looking to migrate from SQL Server 2019 to Azure SQL Managed Instances. Having the same compatibility level can help with doing gradual migrations in different environments over time.
Because you can adjust your Azure DevOps pipeline so that some stages of your deployment pipeline deploy to Azure SQL Managed Instances and others deploy to SQL Server 2019.
For example, deploy to an Azure SQL Managed Instance development environment and then to a SQL Server 2019 UAT environment. Of course, there are other options that you can use to do this as well.
There is a diagram relating to this in an older post of mine about deploying to multiple SQL Server database types using Azure DevOps. Which can help you visualize this better.
Final words
I hope this post about spreading your SQL Server wings with Azure SQL Managed Instances has helped some of you get a better understanding it. Plus, what services are part of Azure SQL.
I highly recommend exploring what you can do with Azure SQL Managed Instances if you have yet to try it. Because it has a lot of potential. Plus, if you have not looked at it for a while it is worth revisiting to check out the newer features.
Of course, if you have any comments or queries about this post feel free to reach out to me.
Be First to Comment