To be discovered: Improved In-Memory OLTP, advanced analytics, enhanced security and opening to other data sources.

SQL Server 2016 was first announced in May 2015 at a Microsoft event. Since June 1st, four versions are available: Enterprise, Standard, Developer and Express. Developer and Express versions are free. One of the main changes, announced several months ago, will be the transition from one license per processor to one license per core.

In-Memory OLTP

SQL Server 2016 is based on the transactional memory processing technology resulting from the "Hekaton" project launched in 2012 and already used by SQL Server 2014. The real innovation with In-Memory is the ability to edit these optimized In-Memory tables after creating there creation. In the 2012 version, the user had to delete and recreate them. In SQL Server 2016, any modification can be made using Transact-SQL code.
Another improvement is the convergence of In-memory and transactional data (OLTP). For example, In-memory optimized tables were capped at 250 GB. In SQL Server 2016, the limit is now 2TB. In-memory transactions are assumed to be 30 times faster than in SQL Server 2014. Undoubtedly, the 2016 version pushes its limits!

Enhanced Security

On the security side, Microsoft is promoting three advances: Always Encrypted, Dynamic Data Masking and RLS.

Always Encrypted (or "full encryption") allows to perform operations on encrypted data, without having to decipher them upstream. Clearly, the data is constantly encrypted (hence the name) whether it is at rest or "in motion" without modifications to the applications being necessary.

Dynamic Data Masking limits the exposure of sensitive data by masking it to unauthorized users. A masking rule can be set to show only the last four digits of the social security or credit card number in the result set of each query.

This masking prevents access to data, but does not modify it in the database (unlike encryption). Its use will certainly be most useful in contexts with strong regulatory constraints and confidentiality.

Third, RLS (aka Row-Level Security) allows database administrators to control who, up to the line level, accesses the data based on the characteristics of the user who is executing the request.

Polybase: Conquering Hadoop

One of the more intriguing features included with SQL Server 2016 is PolyBase, a transparent access layer, a new service that will allow us to use data available in SQL Server, but also in your Hadoop clusters.

While Polybase was part of APS (aka Microsoft Analytics Platform System) well before the announcement of SQL Server 2016, the 2016 version will be the first to integrate it natively. Polybase is a "front-end" that simultaneously "queries" relational data and unstructured or semi-structured data from other sources via T-SQL.

« An infinite database »

On the Cloud side, the new feature of SQL Server 2016 is called Stretch Database. This expression illustrates the idea of ​​a database that extends into SQL Azure, so that the least used data can be stored there while the current data can be kept locally.

Ideally, Stretch Database should answer the classic question of "erasing or not erasing data from an index? ". Rather than inflating a database with old data.  According to Microsoft, these data can be stored at "lower cost" in the Cloud while remaining "locally" queryable.

Even more enhancements

These new features are not an exhaustive list. Other advances include the integration of the R language, support for JSON and HDFS, Query Store, and Temporal Tables.

Want to learn more about the subject? Meet me during these training sessions « Administering a MS SQL Server 2008 / 2012 / 2014 Database » and «Programming a MS SQL Server 2008 / 2012 / 2014 Database » at Technologia.

Eric Vaillancourt
Author: Eric Vaillancourt
Eric Vaillancourt is an SQL enthusiast. He started his career as a database programmer in 1989. He has been teaching SQL at Technologia since 2002 and has trained more than 5,600 professionals. He leads more than fifteen training sessions focused on Microsoft products. Specializing in high availability environments supporting a large number of transactions per second.

Comments (0)

There are no comments posted here yet

Leave your comments

Posting comment as a guest. Sign up or login to your account.
Attachments (0 / 3)
Share Your Location