First Look: New Features in SQL Server 2016

SQL Server 2016 is the latest major release of Microsoft’s premier enterprise data platform. There’s no doubt that SQL Server has been on a fast track release program and the upcoming SQL Server 2016 release will be just two short years after the last SQL Server 2014 release. For business critical enterprise software this is a rapid release cycle that many businesses will have trouble keeping up with. In spite of its short release cycle for a major release there’s no shortage of new features and enhancements in SQL Server 2016. Microsoft’s cloud-first development process has enabled them to introduce many of these features first in their cloud-based Azure SQL Database service before moving them to the on-premise product. In this article you’ll get a first look at the major new features and enhancements in SQL Server 2016.

Simplified SQL Server 2016 Editions

With the SQL Server 2016 release Microsoft has simplified the editions of the SQL Server product family. Notably they have dropped the Business Intelligence edition. Now the basic SQL Server 2016 line-up consists of:

For a complete list of the features found in each of the SQL Server 2016 edition you can refer to Features Supported by the Editions of SQL Server 2016.

Always Encrypted

SQL Server 2016’s Always Encrypted feature enables you to secure sensitive data from all unauthorized access – even from database administrators. Always Encrypted is new with SQL Server 2016 and it’s a client-side encryption technology designed to protect data at rest or in motion. The data is encrypted when it is written to the database and it’s decrypted by the client when that data is accessed by an approved application. Always Encrypted is quite different from SQL Server’s earlier encryption technology Transparent Data Encryption (TDE). TDE provides at-rest data encryption where the data is encrypted when it’s stored on the disk but that data is decrypted by SQL Server when it’s accessed by authorized users. This means that the in-flight data is not encrypted and highly authorized users have access to the data. With Always Encrypted the encryption and decryption of data happens transparently inside the application. The data stored in SQL Server is always encrypted. You can see an overview of how Always Encrypted works in Figure 1.

Figure 1 - Always Encrypted Data Flow

SQL Server 2016’s Always Encrypted is implemented through the use of an Always Encrypted aware database driver that used by the application. In Figure 1 you can see that the data encryption takes place in the Enhanced ADO.NET driver while the data stored in SQL Server is always encrypted. Microsoft has added Always Encrypted support to the .NET Framework 4.6 driver, the Microsoft JDBC 6.0 driver and the Microsoft ODBC 13 driver. The Always Encrypted database driver connects to the database using an encryption key. The data can only be decrypted by using the encryption key. Other applications or data queries can retrieve the encrypted values but the data will not be unencrypted and therefore will remain secure. The SQL Server database engine only works with the encrypted data. The Always Encrypted feature is only in the SQL Server 2016 Enterprise edition.

Row-Level Security

Another new security feature in SQL Server 2016 is Row-level security (RLS). RLS is a feature that other databases have had for some time. Previous releases of SQL Server have not natively had the ability to restrict access to tables at the row level. RLS restricts which rows a given user can view or update in a table based on a function. SQL Server 2016’s new RLS is useful in multi-tenant environments where you may want to limit data access based on customer ID or in healthcare type of situations where you may want to limit access to a patient’s private data.  Figure 2 shows a high-level view of how RLS can limit access to a given row

Figure 2 - Row-Level Security

With RLS the restriction logic is in the database tier rather than the application tier simplifying the design and coding of your applications. RLS supports filter predicates that control which rows are available for read operations and block predicates which explicitly prevent write operations. Row-level security is available in the Enterprise and the Standard editions of SQL Server 2016. It is not available in the SQL Server 2016 Express editions.

Stretch Databases

One of the most important new features in the SQL Server 2016 release is support for Stretch Databases. SQL Server 2016’s Stretch Databases enable you to seamlessly extend a database from your on-premise SQL Server instance to an Azure SQL Database. This enables your frequently accessed or hot data to stay on-premise and your infrequently accessed cold data to be moved to the cloud. Unlike typical cold data storage, your cold data on Azure is always online and available to be queried. Stretch Databases can make your operations more efficient by allowing you to provide longer data retention times using low cost Azure storage. Stretch Databases are completely compatible with Transparent Data Encryption (TDE) as well as the new Always Encrypted and Row-level Security features. You can see an overview of how SQL Server 2016 Stretch Databases work in Figure 3.

Figure 3 - An overview of Stretch Databases

In Figure 3 you can see how user queries directed to your on-premise SQL Server instance can use the Stretch Database feature to transparently access data from the on-premise instance and an Azure SQL Database. SQL Server 2016 will seamlessly retrieve both the local data as well as the remote data in response to the user query. There is some of latency for remote queries but this latency only happens when your queries need to access the remote data. Microsoft provides an Enable Database for Stretch wizard to help you setup Stretch Databases. The wizard enables you to select the database tables that you want to extend to Azure. You can elect to migrate an entire table or you can specify a date-based filter to selectively migrate a portion of the table. Stretch Databases appear on your local system as Linked Servers. The Stretch Database feature is available in all editions of SQL Server 2016

Query Store

Troubleshooting performance problems is always one of the most difficult tasks for the DBA. SQL Server 2016’s new Query Store feature simplifies performance troubleshooting by helping you quickly see the differences in query plan changes over time. In earlier releases of SQL Server you needed to use dynamic management views (DMVs) to see existing execution plans. The DMVs only allowed you to see the execution plans that are actively in the plan cache. There was no visibility for the plans after they went out of the plan cache. The SQL Server 2016 Query Store feature is able to save historical execution plans as well as the query statistics that go with those plans. SQL Server 2016’s Query Store can be enabled on an individual database basis and it automatically captures a history of queries, plans, and runtime statistics. The Query Store separates data by time windows enabling you to see changes and usage patterns. You can see how many times a query has been run, identify the top queries, examine the historical changes in a plan and see the resources required for the plan. You can enable the Query Store from SQL Server Management Studio on the Database Properties page or by running the following T-SQL ALTER DATABASE statement like you can see below.

ALTER DATABASE AdventureWorks2014 SET QUERY_STORE=ON;

In Figure 4 you can see how the Query Store can be used to identify your top resource consuming queries.

Figure 4 – Using the Query Store to Rank Resource consuming queries

The top left pane shows a histogram representing the top resource consuming queries. The bottom pane shows the query’s execution plan. The right pane shows a plan summary for selected query. The Query Store is completely compatible with In-Memory OLTP and is supported in all editions of SQL Server 2016.

Enhanced In-Memory OLTP

First introduced with SQL Server 2014, In-Memory OLTP improves the performance of OLTP queries by moving select tables into memory as well as compiling stored procedure into native x86 code. The In-Memory OLTP engine came with an entirely new optimistic concurrency control lock-free query engine that is designed to work at RAM speed. It works with all standard x86 servers – provided they have enough memory to take advantage of it. Depending on the application, the new In-Memory OLTP engine can provide anywhere from 2x to 30X performance gains. However, the original implement had several limitations with the data types and database features that were supported.

SQL Server 2016 eliminated many of the original restrictions with the In-Memory OLTP implementation. The recommended maximum table size was boosted up to 2 GB from 256 MB. Another great new feature is the ability to combine the Columnstore index with In-Memory OLTP tables essentially doubling down on your in-memory performance enhancements. The FileStream data type is now supported as is Transparent Data Encryption (TDE). Other improvements include support for Foreign Keys and Check Constraints as well as Multiple Active Result Sets (MARS). Nested native procedure calls and natively compiled scalar UDFs are also now supported. In the earlier release you had to drop and recreate the in-memory tables in order to make schema changes. With SQL Server 2016 ALTER TABLE is now partially supported. You can perform off-line operations to add and drop columns, indexes, and constraints. In addition, you can now work with in-memory tables using the SQL Server Management Studio (SSMS) table designer. SQL Server 2016 In-Memory OLTP requires the SQL Server 2016 Enterprise edition.

Enhancements to AlwaysOn

SQL Server 2016 also continues to advance high availability and disaster recovery with several enhancements to AlwaysOn Availability Groups (AG). AlwaysOn AGs provide database level protection and they can protect against planned and unplanned downtime. AGs can protect multiple databases all of which can be automatically failed over as a unit. The failover time is typically just a few seconds. SQL Server 2016 AlwaysOn AGs support up to eight secondary replicas where each secondary replica is located on a separate Windows Failover Cluster node. AlwaysOn AGs can support both synchronous and asynchronous replicas simultaneously. Synchronous replicas are used in high availability scenarios where there is a requirement for fast automatic failover. Asynchronous replicas are typically used for disaster recovery scenarios where the replicas are in a different geographic location or in the cloud

SQL Server 2016 adds several important new capabilities to AGs. SQL Server 2016 will support up to three synchronous replicas as automatic failover targets. SQL Server 2016 AGs will also support the Distributed Transaction Coordinator (DTC) when they are used with Windows Server 2016. In addition, SQL Server 2016 AGs will provide support for round-robin load balancing across readable secondary replicas. There will also be support for automatic failover based on database health. You can see an overview of SQL Server 2016 AlwaysOn Availability Groups in Figure 5.

Figure 5 – SQL Server 2016 AlwaysOn Availability Groups

Figure 5 illustrates how SQL Server 2016’s AlwaysOn Availability Groups can simultaneously support both local synchronous replicas for high availability and automatic failover as well as asynchronous replicas that are geographically dispersed or in the cloud for disaster recover. AlwaysOn Availability Groups require the SQL Server 2016 Enterprise edition

AlwaysOn Basic Availability Groups

One of the major limitations of the previous releases of SQL Server was that the AlwaysOn Availability Groups feature was only available in the Enterprise edition. The more common Standard edition was not able to benefit from them. In the SQL Server 2016 release Microsoft added a limited version of Availability Groups to the Standard edition called AlwaysOn Basic Availability Groups. Basic Availability Groups require Windows Server cluster nodes exactly like regular AlwaysOn Availability Groups. However Basic Availability Groups are limited to two nodes -- a primary and secondary replica. There is no read access to the secondary replica. This means you cannot use the secondary replica for reporting or backup like you can in standard Availability Groups. In addition, like Database Mirroring Basic Availability Groups are limited to a single database. Like you might expect the replicas must be running SQL Server 2016 Standard edition. Basic availability groups cannot be upgraded to advanced availability groups.

PolyBase

Big Data continues to grow in strategic importance but unless you had the SQL Server Parallel Data Warehouse (PDW) integrating SQL Server with Big Data was difficult. In earlier releases, PDW was the only version of SQL Server that came with PolyBase – a technology that bridged SQL Server and Hadoop by enabling you to run SQL queries over Hadoop data stores. SQL Server 2016’s PolyBase feature brings this capability into the main SQL Server family. PolyBase enables you to use your existing SQL Server tools like SSMS and T-SQL to query Big Data stores.  You can access data in Hadoop clusters and even write queries that join this semi-structured Big Data with tables in SQL Server relational databases. With the initial release of SQL Server 2016 PolyBase is able to retrieve external data from the Cloudera and Hortonworks versions of Hadoop and Azure Blob Storage. Microsoft has stated that they intend to add other data sources in the future. You can see an overview of how PolyBase can join data from Hadoop and Azure blob storage in Figure 6.

Figure 6 – Connecting SQL Server 2016 to Big Data using PolyBase

Using PolyBase is much like using a linked server. PolyBase is transparent to the application and the external data sources are incorporated into the database schema. When you use PolyBase to query Hadoop data the PolyBase engine generates Hadoop map reduce jobs on an as-needed basis and then returns the data to SQL Server. PolyBase enables you to incorporate Big Data in your applications as well as in your reporting tools like Reporting Services, Power Query and Power BI. The SQL Server 2016 Enterprise edition can function as a PolyBase head node which can receive queries and a compute node which can work on queries. The SQL Server 2016 Standard edition can only function as a PolyBase compute node.

Native JSON Support and R Integration

Support for JSON (JavaScript Object Notation) is another new feature in SQL Server 2016. JSON is one of the most commonly used standardized data exchange formats. JSON is often chosen when semi-structured information needs to be stored. Many cloud services return information formatted as JSON. In earlier releases of SQL Server you need to manually code complex T-SQL, SQLCLR or JavaScript to perform JSON imports and exports. SQL Server 2016 simplifies this process by incorporating JSON support directly into SQL Server much like its support for XML. However, unlike XML which is its own data type, JSON is represented as an NVARCHAR data type. NVARCHAR is supported in all SQL Server components which enables JSON to be supported by all the database features. JSON can be used with In-Memory OLTP, Columnstore indexes, AlwaysOn Availability Groups and it is compatible with the new security features including row level security. SQL Server 2016 will natively parse and store JSON as relational data and will support exporting relational data to JSON. SQL Server 2016 also provides the ability to load JSON text in tables, extract values from JSON columns, and index properties in JSON text stored in columns. JSON is supported in all editions of SQL Server 2016.

R is a specialized, open source statistical environment that is used as the primary analysis language by many data scientists and statisticians. R is often used to create models and generate predictions.  R Services have been incorporated into the SQL Server 2016 database engine and you can optionally install this feature during the SQL Server setup process. Integrating the R language with SQL Server enables you to keep your analytics processes close to the data eliminating the security risks and performance issues that may exist when you need to move the data off the server for analysis. R code can be executed directly from T-SQL. R Integration requires the SQL Server 2016 Enterprise edition.

Installation Changes for SQL Server Management Studio and SQL Server Data Tools

Microsoft also changed the way SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT) are installed. In previous releases SSMS was installed as a part of the main SQL Server installation process. That is no longer the case with SQL Server 2016. There’s no option to install SSMS from the New SQL Server stand-alone installation of add features to an existing installation option. Instead SSMS is installed separately using the new Install SQL Server Management Tools option. The install process prompts you to download the standalone installer SMS-Setup-ENU.exe. Splitting out the tools enables Microsoft to update the tools on a faster cadence. The new version of SSMS 2016 works with all supported versions of SQL Server from 2008 and higher as well as Azure SQL Database.

With SQL Server 2016 Microsoft also changed the way SSDT was installed. In previous releases Microsoft replaced SQL Server’s BI development tool, Business Development Studio (BIDS), with SQL Server Data Tools. In the process they wound up creating not one but two versions of SQL Server Data Tools both of which needed to be downloaded separately from installing SQL Server itself and both of which performed different functions. With the SQL Server 2016 release Microsoft simplified the installation of SQL Server Data Tools by including it in the SQL Server Installation Center just like SSMS. You now install SSDT using the Install SQL Server Data Tools option. You can download different versions of SSDT. The download page recommends the option Download the Latest SSDT Preview for Visual Studio 2015 built on Visual Studio 2015.   This downloads the file SSDTSetup.exe which installs the project template collection for relational database development, SSAS data models, SSRS reports, and SSIS packages. You can see the new SQL Server Management Studio and SQL Server Data Tools Installation options in Figure 7.

Figure 7 – SQL Server Installation Center

Mobile Device Enabled Reporting Services

Reporting Services has also been significantly upgraded with support for mobile devices. With Microsoft’s recent Datazen acquisition they added mobile capabilities to Report Services reports. Mobile reports are different from standard web reports which often flow between multiple pages. Mobile reports dynamically adjust the content to fit your screen or browser. SQL Server 2016 includes the SQL Server 2016 SQL Server Mobile Report Publisher Tool which enables you to designer and publish mobile reports. Reporting Services new mobile report capabilities includes support for Windows, Apple, Android and HTML 5. You can see a sample of the new mobile reports in Figure 8.

Figure 8 – Reporting Services Mobile Reports

The SQL Server Enterprise Data Platform

SQL Server has clearly evolved from being a small business database in the early SQL Server 6.0 days to the enterprise data platform that SQL Server 2016 is today. SQL Server supports massive scalability with up to 640 logical processor and 12 TB or RAM on Windows Server 2016. Features like AlwaysOn Availability Groups and Always Encrypted provide mission critical availability. SQL Server 2016’s Stretch Databases feature enables seamless cloud integration and the enhancements to Reporting Services enables you to surface SQL Server data and reports to your mobile devices.