How to upgrade SQL Azure server to the latest version

Date created: 5/28/2018.

Last updated: 4/27/2021.

Azure SQL Database always have the latest updates applied, it is automatically updated by Azure.

You cannot rely on @@version (SELECT @@version) to identify which version of SQL Server engine is running your Azure SQL database as it will always show version 12 and it will continue to show version 12 indefinitely. That does not mean that Azure SQL is running as a SQL Server 2014 instance.

The version of the SQL Server database engine run by Azure SQL Database is always ahead of the on-premises version of SQL Server, and includes the latest security fixes. This means that the patch level is always on par with or ahead of the on-premises version of SQL Server, and that the latest features available in SQL Server are available in Azure SQL Database.

To programmatically determine the engine edition, use SELECT SERVERPROPERTY('EngineEdition'). This query will return '5' for Azure SQL Database and '8' for Azure SQL Managed Instance.

You can control how your Azure SQL Database run in compatibility with SQL Server versions by using the compatibility level.

The alignment of SQL versions to compatibility levels are as follows:

100: in SQL Server 2008 and Azure SQL Database
110: in SQL Server 2012 and Azure SQL Database
120: in SQL Server 2014 and Azure SQL Database
130: in SQL Server 2016 and Azure SQL Database
140: in SQL Server 2017 and Azure SQL Database
150: in SQL Server 2019 and Azure SQL Database


To determine the current compatibility level of your database, execute the following Transact-SQL statement:

SELECT compatibility_level
FROM [sys].[databases]
WHERE [name] = 'Your Database Name';

If you wish to use database compatibility level 140 or lower, please execute ALTER DATABASE. For example:

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 140;

To be able to use Query Store, your Azure SQL Database needs to have database compatibility level 130 or above.



 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.