When we install a SQL Server instance on a server, we decide on SQL Version (2012, 2014….), we choose one of available Editions (Standard, Enterprise, etc.) 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:
Azure SQL Database has it’s own naming convention so the current version is called simply V12. Unlike with 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 and then, few months later, they become “generally available”.
At the infrastructure level, not visible to users, SQL Database as a service (PaaS) is currently hosted on SQL Server 2014 instances so the feature set of V12 is very similar to that of SQL 2014 with the following differences:
1. Some of SQL 2014 features are not available in SQL Database, for example – Windows authentication and Memory-optimized tables.
2. Following the recent announcement of SQL 2016 that will be released next year, one of its brand new features known as Query Store has been made available in SQL Database just a few days ago!
Because Azure databases are hosted on SQL 2014, we shouldn’t use SSMS older than 2014 to manage them. And, if we want to play with the Query Store and other upcoming new features, then we should install SSMS 2016 CTP right now.
The difference between the boxed SQL and PaaS databases is also visible in build numbers: while SQL 2014 numbers start with 12, SQL Database codes start with 13 like on the screenshot below.
Edit on November 29th: The version number seems to have changed in the meantime. It now looks like this:
Apparently, my Azure SQL Server was hosted on SQL 2016 CTP 2.4 (13.0.16) at the time of writing the post but now it’s on SQL 2014 RTM 🙂
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEdition; GO
Edition: SQL Azure
The compatibility level of V12 is 120, which was introduced with SQL 2014. And while the boxed product supports 3 different compatibility levels – one current and two previous ones, Azure SQL Database currently supports 4: 100, 110, 120 and 130. This is important to have in mind when we migrate legacy applications, especially if we have 3rd party products that that we can’t modify. Details on 120 features and differences to earlier versions are described at this URL: https://msdn.microsoft.com/en-us/library/bb510680.aspx Here’s just one example with date functions:
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 2011
If you want to change the compatibility level, you will have to do that programmatically because there’s no Properties dialog for Azure SQL Databases that would allow changing database settings through GUI. Here’s the ALTER DATABASE statement for that:
ALTER DATABASE DemoDB SET COMPATIBILITY_LEVEL = 110
SQL 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.