0

Is it possible to set compatibility mode in Azure Sql Database to match SQL Server 2014 ?

We have a existing application using SQL Server 2014 - can i just change the connection string to point to a Azure Sql Database with sql 2014 compatability?

  • 2
    What actual behavioral differences do you expect from a slightly lower compat level? – Aaron Bertrand May 15 '18 at 23:35
1

I am not disgarding anything of @Shanky, but I would like to say that at presently in Azure SQL Server database version is 12.0.2000.8 . Which is the RTM version of SQL Azure and MAJORVERSION of SQL2014.

I am attaching the screen shot here which I have connected to My Azure SQL Server database through on-premises SSMS 2017.

enter image description here

Here I am writing the TSQL query to check the MajorVersion, ProductLevel, Edition, ProductVersion through the below mention query in on-premises as well as in Azure.

SELECT
  CASE 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'     
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017' 
     ELSE 'unknown'
  END AS MajorVersion,
  SERVERPROPERTY('ProductLevel') AS ProductLevel,
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductVersion') AS ProductVersion;
  Go

Here I am just attaching the screen shot of Azure SQL database compatibility level , which I have got after execution of this TSQL query in Microsoft Azure portal.

enter image description here

Even I have also checked out with New Blank database ( which is created by myself) and also checked with Sample (AdventureWorksLT) (this is the sample database which Microsoft is providing at present in the Azure portal).

With the respect of both database I have got the same version with compatibility.

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';

The alignment of SQL versions to default 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

As per Joseph Sack SQL Database Engine Blog documentation here as on dated July 7, 2017 we are announcing the official public preview of compatibility level 140 in Azure SQL Database.

As per MSDN BOL documentation here On Azure SQL Database Managed Instance, this T-SQL feature has certain behavior changes. See Azure SQL Database Managed Instance T-SQL differences from SQL Server for details for all T-SQL behavior changes.

To change the COMPATIBILITY_LEVEL through TSQL.

ALTER DATABASE database_name   
SET COMPATIBILITY_LEVEL = { 140 | 130 | 120 | 110 | 100 | 90 } 

The database_name is the name of the database to be modified.

COMPATIBILITY_LEVEL { 140 | 130 | 120 | 110 | 100 | 90 | 80 }

Is the version of SQL Server with which the database is to be made compatible.

The following compatibility level values can be configured:

 Product       Database Engine Version  Compatibility Level Designation Supported Compatibility Level Values
    SQL Server 2017 (14.x)     14             140                     140, 130, 120, 110, 100
    Azure SQL Database         12             130                     140, 130, 120, 110, 100
    SQL Server 2016 (13.x)     13             130                     130, 120, 110, 100
    SQL Server 2014 (12.x)     12             120                     120, 110, 100
    SQL Server 2012 (11.x)     11             110                     110, 100, 90
    SQL Server 2008 R2         10.5           100                     100, 90, 80
    SQL Server 2008            10             100                     100, 90, 80
    SQL Server 2005            9              90                      90, 80
    SQL Server 2000            8              80                      80  

To Change the compatibility level of SQL Azure database in Microsoft Azure Portal.

ALTER DATABASE CompabilityTestDB   
SET COMPATIBILITY_LEVEL = 140;
Go

I am also attaching the screen shot. enter image description here

After changing to check the compatibility level of Azure SQL database in Azure Portal.

SELECT compatibility_level
FROM   [sys].[databases]
WHERE  [name] = 'CompabilityTestDB';

After changed the compatibility level of Azure SQL database in portal I am also attaching the screen shot here.

enter image description here

Very Important Note: As of January 2018, in Azure SQL Database, the default compatibility level is 140 for newly created databases. We do not update database compatibility level for existing databases. This is up to customers to do at their own discretion. With that said, we highly recommend customers plan on moving to the latest compatibility level in order to leverage the latest improvements.

If you want to leverage database compatibility level 140 for your database overall, but you have reason to prefer the cardinality estimation model of SQL Server 2012 (11.x), mapping to database compatibility level 110, see ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL), and in particular its keyword LEGACY_CARDINALITY_ESTIMATION = ON.

For details about how to assess the performance differences of your most important queries, between two compatibility levels on Azure SQL Database, see Improved Query Performance with Compatibility Level 130 in Azure SQL Database. Note that this article refers to compatibility level 130 and SQL Server, but the same methodology applies for moves to 140 for SQL Server and Azure SQL Database.

For further you ref here , here, here and Default compatibility level 140 for Azure SQL databases

0

Is it possible to set compatibility mode in Azure Sql Database to match SQL Server 2014 ?

Yes it is possible. You have to just use below query to change the compatibility level of he database

ALTER DATABASE database_name   
SET COMPATIBILITY_LEVEL =  130;--DB compatibility of SQL Server 2014 

Please note that if you change compatibility level of database to lower compatibility level you may not be able to use some features available in newer version. For changing compatibility level and understanding its after effect please read View And Change Compatibility Level

can i just change the connection string to point to a Azure Sql Database with sql 2014 compatability.

The default compatibility level of database created on SQL Server 2014 Azure database will be 130, what do you want to change in that ?. Yes you can connect but there would be more than just changing connection string. Please read Next Step Section Of Connecting To Azure Database. It has various links which details about how to use different application to connect to Azure SQL Database

  • ,At presently(in RM portal) in Microsoft Azure portal it's using the SQL Server 2014 database. But in future it will change (i mean to say that SQL Server version support in portal). If the user want to change compatibility level in Azure portal then what to do. – Md Haidar Ali Khan May 16 '18 at 7:15
  • Upgrade it and change the compatibility level. – Shanky May 16 '18 at 7:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.