New Features in SQL Server 2016
Author: Basit A. Farooq
During his keynote speech at Microsoft Ignite conference in Chicago, Microsoft Corporate CEO Satya Nadella announced that the first public preview of SQL Server 2016 will be available this summer. Finally, the wait is over and on the May 27, 2015, the first public preview of SQL Server 2016 was made available for download. SQL Server 2016 is the latest iteration of Microsoft’s flagship database and analytics platform with lots of new features and improved existing features making it an intelligent database and analytics platform for a mobile first, cloud first world. In fact, my first impression of SQL Server 2016 Community Technology Preview 2 is that it is another exciting launch SQL Server product that offers plenty of new features and improved existing features that enable large organizations to deliver outstanding performance for mission-critical applications and deeper insights on the organization’s data across on-premises and cloud.
SQL Server 2016 is expected to be launched in the first half of 2016. In this article, I will briefly cover the new features of SQL Server 2016.
Hybrid Cloud Feature
Unlike previous SQL Server releases, SQL Server 2016 provides powerful hybrid cloud feature called Stretch Database. This feature allows you to silently archive and store the historical data into Azure SQL Database in the cloud. Meaning, current/active table data is stored locally and historical table data is dynamically moved to Azure SQL Database. When you query the data in those table, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly, improving overall performance while reducing overall storage cost and complexity. Good thing is you can enable stretch for a table or database.
In-database R language support
Earlier this year, Microsoft acquired Revolution Analytics, a company specializing in tools and its own open-source distribution for the R programming language, used for statistical analysis. SQL Server 2016 has in-database R programming language support. By incorporating R programming language support into SQL Server 2016 product set makes advanced analytics using R more accessible while making deeper insight into data easier. For example, instead of extracting the data from SQL Server database via ODBC and then analyse it R programming language, you can now able to take your R code to the data, where it will be run inside a sandbox process within SQL Server itself. This eliminates the time and storage required to move the data, and gives you all the power of R and CRAN packages to apply to your database.
PolyBase into SQL Server
In today’s world of interconnected devices, such as smart phones, televisions, watches, laptops, tablets, desktops, gaming devices like Xbox and Play Station, and many more, and wide access to data obtained from various sources, such as Twitter, Facebook, LinkedIn, flat files, blogs, websites, system logs, sensors and so on, data growth is among one of the three main challenges companies are facing today. Therefore, in recent years, organizations are using Apache Hadoop for big data processing from various sources, such as blogs, sensors, social media, system logs and other devices. In previous SQL Server versions, SQL Server PDW was the only release of SQL Server that came with PolyBase that let you perform standard SQL queries over relational data and Hadoop data store (eliminating the need to understand HDFS or MapReduce). However, Microsoft now integrated PolyBase into SQL Server 2016, which means you can now perform SQL queries against non-relational Hadoop data and join it on-the-fly with their existing relational data in SQL Server, eliminating the need of having to pre-load data first into the data warehouse. For example, if your ETL process includes processing of large non-relation data stored in Azure Blob Storage or Hadoop, then you can use simply query this data with standard T-SQL as if it resides in database table. You can run analytics and BI on this data. SQL Server 2016 and PolyBase therefore can help you to build out a hybrid solution that delivers deeper insights on your data, wherever it may be located. This seamless integration between traditional relational structured data and non-relational unstructured data makes SQL Server 2016 a leading enterprise ready database and analytics platform. You can run analytics and BI on this data.
SQL Server 2016 and PolyBase therefore can help you to build out a hybrid solution that delivers deeper insights on your data, wherever it may be located.
Native JSON Support
JSON or JaveScript Object Notation is an open standard format that uses human-readable text to transmit data objects consisting of attribute–value pairs. It is used primarily to transmit data between a server and web application, as an alternative to XML. JSON is currently not natively supported by SQL Server. That means you have to write complex T-SQL, CLR or Java Script code, if you want to parse and store JSON as relational data and to export relational data to JSON. SQL Server 2016 natively supports JSON imports and exports – so you can parse and store JSON as relational data or export relational data to JSON
Always Encrypted
Organizational policies, or industry or government regulations, might require the use of encryption to protect sensitive data stored within databases. SQL Server always provides a variety of encryption options, including column-level encryption, transparent data encryption (TDE), file-level encryption through Windows, backup encryption, and transport-level encryption. These encryption features provide strong security for data at the database and OS levels, preventing the likelihood of unauthorized disclosure of confidential information, even if the SQL Server infrastructure or databases are compromised. The only drawback of these SQL Server encryption feature is that they only secure data at rest and when data is accessed it is not encrypted.
Always encrypted feature of SQL Server 2016 resolved this problem because when this feature is used to encrypt sensitive data, your data will remain encrypted when at rest and in motion. This feature utilizes ADO.NET client-side library and cipher text to encrypt and decrypt data. There is no other relational database management system that provides a feature like Always Encrypted.
Dynamic data masking
Dynamic data masking limits exposure of sensitive data by masking it to the non-privileged users. It helps prevent unauthorized access to sensitive data, allowing customers to designate how much sensitive data to reveal with minimal impact on the application layer data. It is a feature of policy-based security hiding sensitive data in the result set of a query over designated database fields, while the data in the database is not modified. This feature is easy to use with existing applications.
Transparent Data Encryption for in-memory optimized tables
In SQL Server 2008, Microsoft introduced the ability to encrypt an entire database using transparent data encryption. With transparent data encryption, databases can be secured without modifying existing applications, database structures, or processes. It’s the best option to meet regulatory compliance and corporate data security requirements because it encrypts the entire database on the hard disk.
Up until now, the only downside of this feature is that this feature is supported only for disk resident tables and is not supported for in-memory optimized tables. However, SQL Server 2016, TDE is now supported for in-memory optimized tables.
Row Level Security
This new security feature of SQL Server 2016 allows you to control access to data based on user characteristics. This security function is implemented within the database, without requiring modification to the application.
Live Query Statistics
LQS is another new feature of SQL Server 2016 that allows DBAs to view the list of active queries and associated statistics such as current CPU and memory usage information, query execution time, the query progress, and so on. This information is useful to quickly identify potential bottlenecks for troubleshooting query performance issues.
Query Data Store
Query data store is a new feature in SQL Server 2016. This feature behaves similar to the flight data recorder system because it captures every single information about queries such as query itself, query plans, runtime statistics, etc. in a persistent store in the database. It also gives you the ability to bind to the old query plans, if a new query plan cause performance problems. The captured information is useful because it helps you quickly most expensive queries or queries that have degraded or gotten slower over time and you can adjust their performance quickly using the captured data.
Enhancement to In-Memory OLTP engine
First introduced in SQL Server 2014, Microsoft made several improvements to SQL Server 2016 in-memory OLTP engine and related technologies. These are listed as follows:
- You can create columnstore indexes on both on-disk tables and memory-optimized tables.
- Create updateable nonclustered columnstore indexes and to run a columnar index over your in-memory or on disk row store, gives 100x increased performance than what we are experiencing today while conducting operational analytics.
- SQL Server 2016 in-memory engine has now got almost full coverage of T-SQL query surface area, adding support for left outer joins, union all, and distinct syntax.
- Increasing the total amount of memory supported into the terabyte range as well as supporting a greater number of parallel CPUs.
Enhancements to AlwaysOn
With SQL Server 2016 AlwaysOn, you have the ability to have up to three synchronous replicas. It will include DTC (Distributed Transaction Coordinator) support as well as support for load balancing round-robin of the secondary replicas. Finally, SQL Server 2016 AlwaysOn feature also supports automatic failover based on database health.