SQL Server 2016 – Features and Functional Capabilities (Part 1 of 2)

MAY 23, 2016 // By Jared Zagelbaum

Microsoft announced SQL Server 2016 RTM general availability on June 1, 2016 (announcement blog post). To Microsoft’s credit, there are a significant number of new features, increased functionality of existing features, and general performance improvements built into this latest version. The purpose of this article is to group and highlight significant areas of new and existing functionality into related themes and include experience based narratives where considered valuable.

For reference, the latest version of MSDN SQL Server 2016 Release Notes is available here: https://msdn.microsoft.com/en-us/library/dn876712.aspx

Analytics

Continued Investment in SSAS Tabular

Microsoft has made many feature enhancements to SSAS Tabular. The lack of corresponding investment in Multidimensional SSAS says as much as anything as to where MS wants enterprises to move to.

Features of note:

  • Direct Query mode improvements allow for Tabular models to pull directly from relational marts, allowing for disk based solutions that were previously only supportable in Multidimensional.
  • 2016 Tabular metadata, administration, and scripting have been completely redesigned to remove legacy multidimensional dependencies and are formatted in JSON rather than XMLA.
  • DAX language enhanced to support a significant list of new functions and greater abstraction from the logical model (further closing the gap on MDX).
  • Tabular SSAS now available in Standard Edition, albeit some hard memory limits apply.

New SQL Server R Services

MS has strongly incorporated R as its language of choice for advanced analytic solutions. SQL Server 2016 continues this direction by including the new R services feature. The service allows for native development and execution of R solutions using T-SQL, and on the back end, supports multi-threaded and distributed execution. The concept is to quickly operationalize analytics created in R. The reality for most customers is that data management lags far behind for even basic analytic solutions, and the need for manual data cleansing will still require data scientists to work on an ad hoc basis.

PolyBase

Originally available only as part of an appliance (APS/PDW), PolyBase is now available as an on prem install using customer defined architecture. The technology allows simultaneous querying in native T-SQL across relational, Hadoop, and Azure blob storage. The technology has a lot of promise, however, strong data governance is required for the tool to be useable across heterogeneous data stores.

Operational Analytics

Operational analytics is called out as a separate functional area / theme from analytics, as it relies on OLTP systems to support reporting and analytics in lieu of ETL / Data mart based systems. The traditional barriers to use of OLTP schemas to support reporting and analytics are beginning to be broken down by leveraging in-memory technology and more robust metadata management on the part of the RDBMS.

Features of note:

  • In-Memory OLTP. Basically, keeping an entire table in memory instead of on disk. Enhancements in the capabilities of this technology now allow for simultaneous operational and analytic workloads to be performed against the same structure. There are some additional programmatic constructs to learn, but the syntax is just a specialized implementation of T-SQL.
  • Temporal table, a new type of table that allows for point in time querying of current or historical data. The RDBMS tracks changes and manages the complexity of navigating the correct system version based purely on the T-SQL request. Can also be combined with In-Memory OLTP.

This concludes part 1. Check back soon for part 2, where Hybrid Architecture and Administration & Performance will be covered.

If you’d like to contact Magenic directly, email us or call us at 877-277-1044.

Categories // Business Intelligence
Tags // SQL, SQL Server 2016, Microsoft, MSDN, SSAS

Get Started

Contact Us