Attention readers: this article is over 5 years old and outdated. Please read the new article with up-to-date information.
In the on-premises world, when we install a SQL Server on our own servers, we decide on SQL Version (2012, 2014….), Edition (Standard, Enterprise, Express…) and we set the database Compatibility Levels according to our applications’ specs. But what is it like with the Azure SQL Database (PaaS)? The answer is – it’s very different so let’s go through the key points to have in mind:
SQL Version
Azure SQL Database has its own versioning schema, so the current version is called “V12”. Unlike the boxed product, SQL Database feature set is constantly expanding in short release cycles. New features are first made available to a small group of users in so-called “private preview mode”, then they are rolled into “public preview mode” during which they can be changed at any time. Then, few months later, they become “generally available”.
At the infrastructure level, not visible to users, SQL Database as a service (PaaS) is hosted on latest SQL Server builds so the feature set of V12 is very similar to that of the current SQL version with the addition of any new features.
For example, one of cool new features in SQL 2016 known as Query Store was made available in SQL Database long before SQL 2016 release.
Because Azure databases are hosted on latest builds and because of all those new features, end users should always update the SQL Server Management Studio (SSMS) instead of using older versions.
The difference between the boxed SQL and PaaS databases is also visible in build numbers. For on-premises instances, SQL 2014 build numbers start with 12, SQL 2016 with 13 and so on, but with the Azure SQL Database things are little different. See this screenshot:
12? Does that mean that my Azure SQL database is hosted on SQL 2014 RTM?
No. That means that the SQL Azure build numbers are different from those of SQL Server. The version of Azure SQL Database is a combination of edition and product version. ( select SERVERPROPERTY(‘edition’), select SERVERPROPERTY(‘ProductMajorVersion’). The ProductMajorVersion shows 12 which corresponds to the name “V12” for the current Azure SQL DB version.
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEdition, SERVERPROPERTY('edition') AS Edition, SERVERPROPERTY('ProductMajorVersion') As MajorVersion, @@version AS version; GOCompatibility Level
The default compatibility level of V12 is currently (February 2018) set to 140, which is the compatibility level of SQL Server 2017. Beyond that, Azure SQL Database currently supports 4 older compatibility levels: 100, 110, 120 and 130. This is important for the migrations of legacy applications, especially if we have 3rd party products that that we can’t modify. Details on features and differences between compatibility levels are described at this URL: https://msdn.microsoft.com/en-us/library/bb510680.aspx Here’s just one example to illustrate why the compatibility level matters to your code:
SET DATEFORMAT dmy; DECLARE @t2 date = '12/5/2011' ; SET LANGUAGE dutch; SELECT CONVERT(varchar(11), @t2, 106); -- Results when the compatibility level is less than 120: 12 May 2011 -- Results when the compatibility level is set to 120: 12 mei 2011If you want to change the compatibility level, you can do that in the Properties dialog for your Databases or with the ALTER DATABASE statement like this:
ALTER DATABASE DemoDB SET COMPATIBILITY_LEVEL = 110SQL Server Editions
The pricing model has changed with SQL PaaS, there are no Editions with different feature sets and pricing and we don’t have to pay millions for Enterprise Edition upfront just because of a single cool feature while we’ll never need 99% of all others. Instead of Editions there are now Service Tiers that have identical feature sets but with different performance levels and, of course, prices. There’s no upfront cost anymore, billing is per hour and we only pay for what we really use (more on that here). This allow us to start with the smallest/cheapest database, test any features we want, scale it up and down, and then drop it when we’re done.
Hi
how can it be 2 versions on the same server for 2 DB’s?
Pini
LikeLike
Hi, do you know how long Microsoft will give you to upgrade to the latest version once a new version comes out of Azure SQL? Are you forced to upgrade from, for example, V11 to V12 or can you stay on an older version ?
LikeLiked by 1 person
Hi Jon,
I’m not sure but I know that some customers were informed prior to upgrade.
Out of curiosity, is there a particular reason to stick to V11 ?
LikeLike
It was only if features he been removed from the ‘previous’ version that were required for the application
LikeLike
Hey Jon,
You might be interested in this article: http://tinyurl.com/jyo22xj
V11 will probably be available for another while but it’s feature set will not be expanded.
LikeLike
If my DB is hosted on a SQL 2014 RTM, then how could it possibly support compatibility level of 130 ? Isn’t 130 meant for SQL 2016 ? Then how do I get all the 2016 features (JSON support etc.) on my Azure DB which is marked as V12 but still hosted on SQL 2014 ?
LikeLike
Hi Progmars,
V12 is a feature sets that’s being continuosly expanded. I’m pretty sure that SQL Server builds hosting Azure SQL Databases are not same as the boxed products, otherwise we wouldn’t have new features like JSON, DDM or RLS way ahead of SQL 2016 release.
LikeLike
There is a typo… three times you said MayorVersion instead of MajorVersion, twice in the description before the code, and once for the column header in the code.
LikeLike
Oh, that’s embarrassing. Thank you for pointing that out, Kalen!
I have corrected typos and updated some of the information to reflect changes that happened since the original posting in 2015.
LikeLike
Hi, Am trying to deploy dacpac created with SQL Server 2017 in Azure SQL V12 and getting an error, “A project which specifies SQL Server vNext CTP as the target platform cannot be published to Microsoft Azure SQL Database v12”
Does Azure SQL doesnot support compatibility level 140?
Can anyone Help me on this?
LikeLike