SELECT features FROM bumf... What's new in MS SQL Server 2016

Tasty new stuff from Microsoft – but you need Enterprise Edition for full use

Analytics – now with R

SQL Server 2016 is the first release since Microsoft's acquisition of Revolution Analytics. From this comes SQL Server 2016 R Services, composed of the R language runtime, Intel's Math Kernel Library, which optimizes R operations, and R packages which run on SQL Server. The ScaleR library, developed by Revolution Analytics, includes algorithms for data import; sorting, merging, and splitting; statistical functions and cross tabulation; data visualization; modeling algorithms and decision trees.

Microsoft has also enhanced its support for tabular models, a feature of Analysis Services which uses relational rather than OLAP modeling. Direct Query, which queries live external data rather than importing it first, now supports more data sources, including Azure SQL, Oracle and Teradata. There are also numerous new DAX (Data Access Expressions) functions, used by Analysis Services.

Reporting Services – new web portal and mobile reports

Microsoft has thoroughly revamped Reporting Services in this release. At long last, the report rendering engine has been redesigned to support HTML5, meaning better support for modern web browsers including Edge, Chrome, Firefox and Safari. New chart types have been added, for Treemap and Sunburst charts. There is also a new mobile reports feature, based on Microsoft's acquisition of Datazen Software in early 2015.

Reporting Services has been updated for mobile and HTML5

Reporting Services has been updated for mobile and HTML5

SQL Server 2016 editions

There are four main editions of SQL Server:

Express: free version limited to the lesser of 1 CPU socket or 4 cores, 1GB of RAM per database engine, 4GB per instance of Reporting Services, and 10GB database size. This edition is designed for small local databases or non-critical web applications, and there is a long list of missing features.

Web: available only through hosting providers, the Web edition is limited to the lesser of 1 CPU socket or 16 cores, 64GB of RAM per database engine, and 64GB per instance of Reporting Services. Database size is not restricted. Still lots of missing features, but much better than Express and modestly priced.

Standard: limited to the lesser of 4 CPU sockets or 24 cores, 128GB RAM per database engine, and 64GB per instance of Reporting Services. Database size is not restricted. Includes some availability features, row-level security, some analysis and business intelligence support, fuller Reporting Services than Express or Web.

Enterprise: all SQL Server features. Enterprise-only features include Always Encrypted (and the earlier Transparent Data Encryption); in-memory Column Store and OLTP; data compression; table partitioning; R integration, and some PolyBase features.

In addition, there is a free Developer edition which is the same as Enterprise but not licensed for production use.

Summing up

Two years of effort combined with some shrewd acquisitions makes SQL Server 2016 a substantial upgrade to what has always been a solid product. It is a shame, though, that some key features, including Always Encrypted and in-memory OLTP, are reserved for the Enterprise Edition, making it an expensive option for smaller organizations.

Additions like Query Store and Temporal Tables, while not breaking any ground technically, look as if they will be immediately useful, and fortunately both these features are available in all editions.

Microsoft likes to brag about SQL Server's cost advantage over other enterprise vendors, but that is easy pickings. It is up against some strong open source offerings, along with cloud competition from the likes of Amazon Aurora, and in this context it looks expensive.

Even so, as noted above, SQL Server is more than just a database server and is now a feature-rich offering that will help Microsoft retain its market share.

Pricing and licensing

Enterprise: Core-based licensing only – no Client Access Licenses (CALS) but you pay per CPU core. Open licence price $14,256 per 2 cores.

Standard: CAL or Core-based licensing. CALs $931 plus $209 per user or device; $3717 per 2 cores

Prices are taken from the price list here.

A typical price for the Web edition, from hosters only, is £25 per month. ®

Sponsored: Beyond the Data Frontier


Biting the hand that feeds IT © 1998–2019