A long time ago, in early days of the Azure SQL Database in 2015, I wrote this article in which I tried to compare the SQL Server and Azure SQL Database in terms of versions and compatibility levels. Over the years, that article has attracted many readers and comments through search engines and referrals and that trend continues even today. But the article is outdated so it’s time to re-write it and clarify a few things.
There’s been a lot of developments since 2015, SQL Server got two new versions and it even runs on Linux and Docker! And then, there’s the SQL Managed Instance in addition to Azure SQL Database. Let’s see how they compare …
Database engines
The database engines for: SQL Server on Windows, SQL Server on Linux, Azure SQL Database and Azure SQL Managed Instance all share the same code base. There is no separate code for Windows, Linux or Azure. Those engines share the same T-SQL language and query processing logic. You can move a database from one of these products to another. You can take a backup on Linux and restore it on Windows or vice versa, just like you always did between two Windows servers.
Versions and Compatibility
The common code base is continuously developed and expanded with new features. The Azure SQL Database is the first to receive improvements through continuous delivery, so its engine is same or ahead of that in the current SQL Server version.
On the other side, when a new version of SQL Server is released, it brings an expanded feature set, a new engine version number, and a new compatibility level. At the same time, Azure SQL Database gets the new default compatibility level equal to that of the SQL Server. The current default is 150 which corresponds to SQL Server 2019. Keep in mind that Microsoft does not automatically update the compatibility level for existing Azure SQL databases, it is up to customers to do that at their own discretion.
You can check your compatibility in SSMS when you open databases properties dialog or by executing the following query:
SELECT name, compatibility_level FROM sys.databases;All SQL products retain backward compatibility through support for previous compatibility levels. This allows for upgrades of legacy databases to a newer version of the server or migration to the cloud. The following table shows the supported compatibility levels for the current and recent versions of the products:
Product Database Engine version Compatibility Level Supported Compatibility Levels SQL Server 2019 15 150 150, 140, 130, 120, 110, 100 Azure SQL Database 12 150 150, 140, 130, 120, 110, 100 Azure SQL Database Managed Instance 12 150 150, 140, 130, 120, 110, 100 SQL Server 2017 14 140 140, 130, 120, 110, 100 SQL Server 2016 13 130 130, 120, 110, 100 SQL Server database versions and compatibility levels Unlike SQL Server, Azure SQL Database has a version number that hasn’t changed since 2015. The database engine version numbers for SQL Server and Azure SQL Database are not comparable with each other, and they are really internal build numbers for these separate products. Due to continuous delivery, version 12 of Azure SQL Database is actually newer than version 15 of SQL Server.
Execute this on an Azure SQL database out and you will see the same build number as was displayed 5 years ago:
SELECT @@VERSIONMicrosoft SQL Azure (RTM) - 12.0.2000.8 Aug 28 2020 10:26:13
Copyright (C) 2019 Microsoft Corporation
Migration considerations
If you are planning to migrate from SQL Server to Azure SQL Database, try and think in terms of feature sets and compatibility levels instead of product versions. If you have applications that are certified to work with certain versions of SQL Server, it is a recommended to start certifying based on compatibility level instead. This approach brings a number of benefits: decoupling application certification from the platform, reducing upgrade risks, and upgrading with no code changes.
Further Reading