Microsoft SQL Server 2016 is a wrap
Microsoft SQL Server 2016, the latest version of Redmond's core operational database, has reached GA. This release, which has been available in public preview for just over a year, is probably one of the biggest releases ever for the product. Major features have been added to the relational, business intelligence and information management components of the product, and brand new in-database advanced analytics capabilities appear in the SQL Server for the first time.
Redmond has leveraged a great deal of its own technologies, including those coming from its research arm, and combined them with technology from recent acquisitions, to produce a juggernaut release. What follows is a rundown on what's on offer.
Relational engine
- In-memory OLTP has been improved, eliminating many of the "no-go" scenarios that plagued this part of the product in its initial release in SQL Server 2014
- Compatibility with JSON (JavaScript Object Notation)-formatted data, giving SQL Server some of the same schema flexibility and modern developer appeal as several document store NoSQL databases, like MongoDB.
- Dynamic Data Masking, Row-Level Security and Always Encrypted features have been added to the product, bringing data protection capabilities to the SQL Server platform that previously could only be implemented through the use of third party solutions
- Stretch Database, a technology that allows on-premises and Azure cloud hybridized database storage has been added. The pricing model here may require some evolution before this feature is relevant to many customers, but the underlying technology is impressive
- Some hardcore features, like Temporal Tables and Query Store, which help track data lineage/history and tune query performance, respectively
Data warehouse, Big Data
- Columnstore indexes have been vastly improved, essentially allowing data warehouse-style queries to be performed on OLTP data, and OLAP tools to query data warehouses and data marts directly, without necessarily requiring separate OLAP cubes
- PolyBase, Microsoft's SQL Server-Hadoop integration technology, is now in the mainstream Enterprise version of SQL Server. It was previously available only in specialized parallel data warehouse versions of the product
Business Intelligence
- The Tabular mode of SQL Server Analysis Services (SSAS), first introduced in SQL Server 2014 and based on the same technology used in Excel, Power Pivot and Power BI, has been greatly enhanced, bringing its enterprise features to near parity with the tried-and-true Multidimensional mode of SSAS. The Tabular model's underlying DAX language has also been greatly enhanced.
- SQL Reporting Reporting Service (SSRS), which had not seen meaningful enhancement since 2010, now has an updated look and feel, greatly enhanced HTML 5 capabilities (and browser compatibility) and includes Mobile Reports, essentially an integrated version of the technology Microsoft gained from its 2015 acquisition of Datazen. SSRS has some Power BI integration, too
Information management
- SQL Server Integration Services (SSIS) -- Microsoft's Extract, Transform and Load (ETL) tool gets numerous new features this release -- including enhancements for Hadoop connectivity
- Master Data Services (MDS), Microsoft's master data management solution, originally based on technology gained from the 2007 acquisition of Stratature, has seen a great deal of investment, both in its standalone and Excel-based user interfaces. MDS had been blowing in the wind for some time, so this is good to see. Now we have to monitor whether Microsoft really wants to compete in the MDM space or is content with just having a more stable version of a tool that carries up the rear
Advanced analytics
- The R programming language is now integrated into the relational engine, allowing for operationalized statistical analysis and predictive analytics to be performed, through the developer-familiar stored procedure interface SQL Server has had since inception. branded SQL Server R Services, this implementation is based on technology Microsoft added to its portfolio when it acquired Revolution Analytics in 2015.
With this new version, Microsoft has paid attention to, and jumped on, important trends in the database market: in-memory database engine operation, NoSQL/document storage, Big Data/Hadoop integration, in-database analytics and machine learning, hybrid cloud, mobile reporting and data breach detection.
The 2016 release of SQL Server is probably the most comprehensive update to the product since it was first released. Moreover, iterative innovation in, and better parity with, its cloud cousin, Azure SQL Database, as well as an ongoing effort on a Linux version of the relational engine, mean huge forward momentum for this product, both despite and due to its nearly-quarter century history.
See also: