Microsoft SQL Server 2016

SQL Server 2016 is a version of Microsoft's relational database management system (RDBMS) that first became available in preview releases during 2015, with general availability on June 1, 2016. SQL Server 2016 is a SQL-based database designed to support a mix of transaction processing, data warehousing and analytics applications in enterprise environments.

In announcing plans for the SQL Server 2016 release in May 2015, Microsoft officials said the updated database was part of the company's move toward "mobile first, cloud first" IT deployments. For example, the new features built into SQL Server 2016 include one that lets users "stretch" their on-premises databases into the cloud; in addition, several added features were first tested and validated in Azure SQL Database, Microsoft's cloud-based cousin to SQL Server. SQL Server 2016 also supports business intelligence applications on mobile devices, another difference from its immediate predecessor, SQL Server 2014.

Here are details on the new and enhanced features included in SQL Server 2016.

PolyBase isn't a new feature overall, having been part of the Microsoft Analytics Platform Service previously, but it is new to SQL Server in the 2016 release. Polybase is a transparent access layer that helps connect the database engine to external data sources containing unstructured or semi-structured data. PolyBase lets users use constructs from T-SQL, Microsoft's version of SQL, to connect to and query unstructured data the same way they can query data in the traditional database. In SQL Server 2016, PolyBase lets user access data in Hadoop systems or Azure blob storage.

In-Memory OLTP was first introduced in SQL Server 2014, but it has been improved in SQL Server 2016. In-Memory OLTP is a component integrated into SQL Server that lets users run online transaction processing (OLTP) applications against database tables stored in system memory instead of on disks. In SQL Server 2016, Microsoft increased both the scalability and performance of In-Memory OLTP. It increased the recommended size of memory-optimized tables, and added multiple log threads and the ability to create multiple plans for queries accessing in-memory tables. SQL Server 2016 also provides the ability to modify memory-optimized tables after they've been created, and improves the In-Memory OLTP capabilities of T-SQL.

AlwaysOn Availability Groups were originally introduced in SQL Server 2012. This SQL Server feature supports high availability and disaster recovery capabilities as an alternative to database mirroring. New features for AlwaysOn Availability Groups  in SQL Server 2016 include failover triggered by problems with database health, support for Microsoft's Distributed Transaction Coordinator service, synchronous replicas, optimized log transport, group Managed Service Accounts and load balancing across secondary replicas.

Stretch Database is a completely new feature in SQL Server 2016. It allows a database to be hosted partially on-premises and partially in the cloud on Azure SQL Database. The on-premises and cloud data sets can be accessed transparently and queried simultaneously. Typically, more frequently accessed data would be stored on premises while less frequently accessed data would be put in the cloud. That lets users expand the storage capacity of databases without having to buy expensive enterprise-class storage.

JSON support is included for the first time in SQL Server 2016. It allows users to read and write documents based on the JavaScript Object Notation (JSON) data-interchange format.

SQL Server R Services is another new feature in SQL Server 2016 that enables users to make use of the open source R programming language, which supports advanced analytics applications.

Dynamic data masking is a new security feature added to SQL Server 2016. It lets SQL Server database administrators hide some of their data from all but specific privileged users. Ordinary users will see some of the data blanked out, while the users with full access privileges can view all of the data. The data masking occurs during the output process based on the status of the person trying to access the data.

Always Encrypted is another security feature introduced as part of SQL Server 2016. With it, DBAs or other users can perform operations on encrypted data without decrypting it. As a result, the encrypted data is never rendered as plain text and remains encrypted both at rest and in motion.

This was last updated in May 2016

Continue Reading About Microsoft SQL Server 2016

Dig Deeper on Microsoft SQL Server Installation