This blog post was co-authored by Eric Hudson, Senior Product Marketing Manager, Data & AI.
This is the second blog of a series focused on SQL Managed Instance, where we will dive into technical topics that will help you successfully modernize your SQL Server workloads in Azure. In this blog, you will learn how SQL Managed Instance is closing the gap on SQL Server.
When you migrate from any SQL Server to SQL Managed Instance, you want to be sure your apps will run without the need to make any changes. The higher degree of this compatibility means potentially less refactoring and one less headache for you to think about. It is also important to know you have access to the most current features on the market that will support the migration of mature SQL Server workloads.
“We wanted an easy transition from on-premises SQL Server, and Azure SQL Managed Instance looks just like SQL Server—with all the operational benefits of a platform as a service.” – Hardayal Singh, Senior Principal Enterprise Architect, City National Bank
Compatibility levels offer more features
To assist in database modernization, from an older version of SQL Server to SQL Managed Instance. Upgrade experiences vary, and some can be quite complicated. Depending on the version you are upgrading from, some SQL Managed Instance features might not be . In this case, features like log shipping or Windows Server failover clustering may have a cloud-based alternative. When you upgrade successfully, you'll not only maintain the features of your traditional SQL Server, but you will be adding the newest features beyond the latest in-market available version of SQL Server and integrations with other Azure services.
How do compatibility levels work?
Each version of SQL Server is assigned a default compatibility level designation number and is compatible with a limited number of previous versions. For example, the compatibility level designation is 150 for SQL Server 2019 and is compatible with 140, 130, 120, 110, and 100. As of early 2022, SQL Managed Instance supports all compatibility levels starting with 100.
For example, you plan to upgrade your SQL Server version to SQL Server 2022, but you know the databases that reside on that SQL Server instance run on a lower compatibility level . After upgrading to the newest SQL Server version, you can keep the original compatibility level version for stabilizing your performance, if it makes sense for your workload. However, if you want to use newer workload performance improvements, you can do this by assigning a corresponding compatibility level number to your database. This number allows the behavior of the database to be compatible with the specific version of SQL Server it is running on.
Coming soon: SQL Server 2022 will support compatibility level designation 160. This will allow your databases to have the highest level of compatibility with SQL Managed Instance, giving you the most innovative features.
To change the compatibility level, in the SQL Server Management Studio, choose your database in Object Explorer. Then, right-click on the database name and select the Properties. In the dialog, select the Options page and in the Compatibility level field, select the SQL Server version. The associated compatibility level number follows the SQL Server version name.
Closing the application compatibility gap with SQL Server parity
SQL Managed Instance preserves all PaaS capabilities, which drastically reduces management overhead and total cost of ownership (TCO). Azure SQL Managed Instance will also modernize your existing SQL Server applications at scale with almost 100% feature parity with the SQL Server database engine. The database engine component of SQL Server is the core service for , retrieving, processing, and securing data. It provides controlled access and rapid transaction processing to meet the requirements of the most demanding data-consuming applications in your enterprise.
Common compatibility areas of concern for businesses are protecting sensitive data, maintaining high availability, preparing for disaster recovery, and limiting the need to rewrite code, as described in this section. For a complete list, see the SQL Server features and SQL Managed Instance list.
The need to protect sensitive data is at the top of the list for businesses. SQL Server databases use the Always Encrypted feature to store sensitive information such as credit card numbers or national identification numbers (for example, U.S. social security numbers) by guaranteeing that unauthorized users are not able to decrypt it. When you migrate to SQL Managed Instance, the Always Encrypted feature is fully supported. Always Encrypted can be configured using Windows cert store and Azure key vault.
Businesses are concerned with maintaining high availability, allowing a high uptime rate to ensure business continuity. A low uptime rate or an unplanned outage would disrupt this. Having a disaster recovery plan in place is paramount. SQL Server uses the feature to maximize the availability of a set of user databases for an enterprise. When you migrate to SQL Managed Instance, SLA with 99.99% availability. You'll also have options for disaster recovery depending on your application requirements and will be able to take advantage of the auto failover groups feature to configure a secondary SQL Managed Instance in another region.
Check back for Part 4 in this technical blog series, What you need to know about business continuity in Azure SQL Managed Instance, for details on auto-failover groups.
SQL Server contains many built-in functions to support building database queries. If you have relied on the built-in functions in SQL Server over many years, chances are you've developed quite a large set of custom queries. Once you migrate to SQL Managed Instance, you'll want your apps to be compatible, requiring little to no changes.
In addition to supporting existing SQL Server functions, SQL Managed Instance offers new functionality. For example, the GREATEST and LEAST statistical functions are already available on Azure SQL Managed Instance. They will be available with the release of SQL Server 2022 for the SQL Server line of products. We are constantly working toward closing the gap in available features in SQL Managed Instance ( For a complete list of SQL Server features supported by SQL Managed Instance, see this article.
For a successful migration from SQL Server to Managed Instance, you should be aware of the technical characteristics and resource limits in Azure SQL Managed Instance. Hardware generation, characteristics and limits depend on the underlying infrastructure and architecture. Each of the two service tiers, General Purpose and Business Critical, has its own set of different capabilities in SQL Managed . For a discussion of the performance differences between SQL Server and SQL Managed Instance, go to For an in-depth description and information on how to request an increase to these limits, go to Overview of Azure SQL Managed Instance resource limits.
Fully managed and always up to date
With SQL Managed Instance, you'll spend more time innovating and less time patching, updating, and backing up your databases. Azure automatically applies the latest updates and patches so that your databases are always up to date. This will eliminate worrying about end-of-life or end-of-support for your apps. As an alternative, you can select a maintenance window to minimize the impact of the updates. For example, you can select the maintenance window for the automated patching process of your SQL Managed Instance so that you can control the timing and impact of the patching process.
Complex tasks like high availability, disaster recovery, and backups are automated, freeing you to focus on business goals. With SQL Managed Instance, you won't need to do any costly upgrades or assessments—we'll do it for you.
Try it out
- Get started with SQL Managed Instance
- Find How-to topics for SQL Managed Instance
- Explore the comprehensive SQL Server Managed Instance documentation
Future topics in this series
Stay tuned for more blogs in the Azure SQL Managed Instance: Technical blog series:
- Performance and scale: Optimize performance for your demanding workloads.
- Business continuity: Use mechanisms, policies, and procedures that enable your business to continue operating in the face of disruption.
- Securing your data on SQL Managed Instance: Build and maintain a strong security posture.
- Migration tools: Learn about free migration tools and programs to help you on your journey to the cloud.
Past topics in this series
In case you missed it, catch up on the previous blog in this series, A guide to modernization vs. migration: Which is right for you? Part 1. That blog includes how to choose between IaaS and PaaS.
Microsoft
