We are happy SQL Server 2016 has released. SQL Server 2016 provides breakthrough performance for mission critical applications and deeper insights on your data across on-premises and cloud. Top capabilities for the release include: Always Encrypted – a new capability that protects data at rest and in motion, Stretch Database – new technology that lets you dynamically stretch your warm and cold transactional data to Microsoft Azure, enhancements to our industry-leading in-memory technologies for real-time analytics on top of breakthrough transactional performance and new in-database analytics with R integration.
Unique in this release of SQL Server, we are bringing capabilities to the cloud first in Microsoft Azure SQL Database such as Row-level security and Dynamic Data Masking and then bringing the capabilities, as well as the learnings from running these at hyper-scale in Microsoft Azure, back to SQL Server to deliver proven features at scale to our on-premises offering. This means all our customers benefit from our investments and learnings in Azure.
Key Capabilities in SQL Server 2016 CTP2
Always Encrypted, based on technology from Microsoft Research, protects data at rest and in motion. With Always Encrypted, SQL Server can perform operations on encrypted data and best of all, the encryption key resides with the application in the customers trusted environment. Encryption and decryption of data happens transparently inside the application which minimizes the changes that have to be made to existing applications.
This new technology allows you to dynamically stretch your warm and cold transactional data to Microsoft Azure, so your operational data is always at hand, no matter the size, and you benefit from the low cost of Azure. You can use Always Encrypted with Stretch Database to extend data in a more secure manner for greater peace of mind.
Real-time Operational Analytics & In-Memory OLTP
For In-Memory OLTP, which customers today are using for up to 30x faster transactions, you will now be able to apply this tuned transaction performance technology to a significantly greater number of applications and benefit from increased concurrency. With these enhancements, we introduce the unique capability to use our in-memory columnstore delivering 100X faster queries on top of in-memory OLTP to provide real-time operational analytics while accelerating transaction performance.
Additional capabilities in SQL Server 2016 CTP2 include:
- PolyBase – More easily manage relational and non-relational data with the simplicity of T-SQL.
- AlwaysOn Enhancements – Achieve even higher availability and performance of your secondaries, with up to 3 synchronous replicas, DTC support and round-robin load balancing of the secondaries.
- Row Level Security– Enables customers to control access to data based on the characteristics of the user. Security is implemented inside the database, requiring no modifications to the application.
- Dynamic Data Masking – Supports real-time obfuscation of data so data requesters do not get access to unauthorized data. Helps protect sensitive data even when it is not encrypted.
- Native JSON support – Allows easy parsing and storing of JSON and exporting relational data to JSON.
- Temporal Database support – Tracks historical data changes with temporal database support.
- Query Data Store – Acts as a flight data recorder for a database, giving full history of query execution so DBAs can pinpoint expensive/regressed queries and tune query performance.
- MDS enhancements – Offer enhanced server management capabilities for Master Data Services.
- Enhanced hybrid backup to Azure – Enables faster backups to Microsoft Azure and faster restores to SQL Server in Azure Virtual Machines. Also, you can stage backups on-premises prior to uploading to Azure.
New Feature in SQL Server 2016
One common problem many organizations face when upgrading versions of SQL Server is changes in the query optimizer (which happen from version to version) negatively impacting performance. Without comprehensive testing, this has traditionally been a hard problem to identify and then resolve. The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed. The Query Store is configured at the individual database level.
Hadoop and Big Data have been all the rage in the last several years. I think some of that was industry hype, but Hadoop is a scalable, cost-effective way to store large amounts of data. Microsoft had introduced Polybase, a SQL Server connector to Hadoop (and Azure Blob Storage) to its data warehouse appliance Analytics Platform System in 2015. But now Microsoft has incorporated that functionality into the regular on-premises product. This feature will benefit you if your regular data processing involves dealing with a lot of large text files — they can be stored in Azure Blob Storage or Hadoop, and queried as if they were database tables. A common scenario where you might use this would be an extract, transform and load (ETL) process, where you were taking a subset of the text file to load into your database.
One common idiom in recent years, is how cheap storage is. While it may be cheap to buy a 3TB drive from Amazon, if you are buying enterprise-class SAN storage or enterprise SSDs, you will know that storage is still very expensive. Microsoft is trying to help reduce your storage (and processing costs) with a hybrid feature called “Stretch Database.” The basics of Stretch Database are that some part of your tables (configurable or automated) will be moved into an Azure SQL Database in the cloud in a secure fashion. When you query those tables, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly. The query processing on the Azure rows takes place in Azure so the only latency is for the return of the rows over the network. As an additional enhancement, you are only charged for the SQL Database in Azure when it is used for queries. You do, however, pay for the Azure Blob storage, which, generally speaking, is much cheaper than enterprise storage.
In addition to supporting direct querying to Hadoop, SQL Server 2016 adds support for the lingua franca of Web applications: Java Script Object Notation (JSON). Several other large databases have added this support in recent years as the trend towards Web APIs using JSON has increased. The way this is implemented in SQL 2016 is very similar to the way XML support is built in with FOR JSON and OPENJSON — providing the ability to quickly move JSON data into tables.
A feature that other databases have had for many years, and SQL Server has lacked natively is the ability to provide row-level security (RLS). This restricts which users can view what data in a table, based on a function. SQL Server 2016 introduces this feature, which is very useful in multi-tenant environments where you may want to limit data access based on customer ID. I’ve seen some customized implementations of RLS at clients in the past, and they weren’t pretty. It is hard to execute at scale. The implementation of RLS in SQL 2016 still has it limits (updates and inserts are not covered), but it is good start on a much-needed feature.
It seems like every month, we hear about some company having a major data breach. Encryption works, but many companies do not or cannot implement it all the way through the stack, leaving some layer data available for the taking as plain text. SQL Server has long supported both column-level encryption, encryption at rest, and encryption in transit. However these all had to be configured independently and were frequently misconfigured. Always Encrypted is new functionality through the use of an enhanced client library at the application so the data stays encrypted in transit, at rest and while it is alive in the database. Also given Microsoft’s push towards the use of Azure, easy encryption makes for a much better security story.
SQL Server 2014 introduced the concept of in-memory tables. These were optimally designed for high-speed loading of data with no locking issues or high-volume session state issues. While this feature sounded great on paper, there were a lot of limitations particularly around constraints and procedures. In SQL Server 2016, this feature is vastly improved, supporting foreign keys, check and unique constraints and parallelism. Additionally, tables up to 2TB are now supported (up from 256GB). Another part of in-memory is column store indexes, which are commonly used in data warehouse workloads. This feature was introduced in SQL 2012 and has been enhanced in each version since. In 2016 it receives some enhancements around sorting and better support with AlwaysOn Availability Groups.