SQL Server 2016 New Features - 7 May 2017 15:56

Contents

• 0. Initial setup
• 0.1 Release notes
• 0.2 Demo database setup
• 1. Installation
• 1.1. Additional Installation Requirements above SQL Server 2014
• 2. Database Engine
• 2.1 Database Engine Feature Enhancements
• 2.1.1 Columnstore Indexes
• 2.1.2 In-Memory OLTP
• 2.1.3 Live Query Statistics (CTP 2.0)
• 2.1.4 Query Store
• 2.1.5 Temporal Tables
• 2.1.6 Backup to Microsoft Azure
• 2.1.7 Managed Backup
• 2.1.8 Trace Flag 4199
• 2.1.9 JSON
• 2.1.10 Always Encrypted
• 2.1.11 PolyBase
• 2.1.12 Stretch Database
• 2.1.13 TempDB Enhancements
• 2.2 Transact-SQL Enhancements
• 2.3 System View Enhancements
• 2.4 Security Enhancements
• 2.4.1 Row Level Security
• 2.4.2 Dynamic Data Masking
• 2.4.3 New Permissions
• 2.4.4 Transparent Data Encryption
• 2.5 High Availability Enhancements
• 2.6 Tools Enhancements
• 2.7 Replication Enhancements
• 3. Analysis Services
• 3.1 New elements in MS-CSDLBI 2.0 schema (CTP 1.0)
• 3.2 Easier setup for Analysis Services Power Pivot Mode (CTP 1.0)
• 3.3 Add Computer Accounts as Administrators in Management Studio (CTP 2.0)
• 3.4 Parallel processing for multiple table partitions in Tabular models (CTP 2.0)
• 3.5 New DAX Functions for Tabular models
• 3.6 Improved query generation for DirectQuery (CTP 2.3)
• 3.7 New behaviors for DAX variables (CTP 2.3)
• 3.8 Extended Events updates (CTP 2.3)
• 3.8 DBCC for Analysis Services (CTP 3.0)
• 3.9 Analysis Services Management Objects (AMO) updates (CTP 3.0)
• 3.10 Administration of Tabular 1200 models in SSMS (CTP 3.0)
• 3.11 Support for Visual Studio Configuration manager (CTP 3.0)
• 3.12 Saving incomplete measures (CTP 3.0)
• 3.13 Formula Fixup (CTP 3.0)
• 3.14 Improved SSDT modeling performance for Tabular 1200 models (CTP 3.0)
• 3.15 Improved DAX formula editing (CTP 3.0)
• 3.16 Project templates added for Tabular 1200 models in SSDT (CTP 3.0)
• 3.17 New data sources for DirectQuery mode (CTP 3.0)
• 3.18 Partition management for Tabular 1200 models in SSMS (CTP 3.1)
• 3.19 Upgrade, roles, and metadata visualization for Tabular 1200 models in SSDT (CTP 3.1)
• 3.20 Set default for bi-directional cross filters in tabular models in SSDT (CTP 3.2)
• 3.21 New DirectQuery implementation (CTP 3.2)
• 3.22 Calculated tables in SSDT (CTP 3.2)
• 3.22 Tabular Model Scripting Language (TMSL) supported in SSMS (CTP 3.2)
• 3.23 Enable/disable attribute hierarchies ??
• 4. Reporting Services
• 4.1 Microsoft .NET Framework 4 (CTP 2.0)
• 4.2 Report Builder for SQL Server 2016 supports High DPI (Dots Per Inch) scaling and devices (CTP 2.0)
• 4.3 Enhancements to Reporting Services Subscription features
• 4.4 Treemap charts(CTP 2.3)
• 4.5 Sunburst charts (CTP 2.3)
• 4.5 New Report Builder User Interface (CTP 2.3)
• 4.6 HTML 5 Rendering Engine (CTP 2.3)
• 4.7 Custom Parameters Pane (CTP 2.4)
• 4.8 PDF Replaces ActiveX for Remote Printing (CTP 2.4)
• 4.9 PowerPoint Rendering and Export (CTP 2.4)
• 4.10 SQL Server Data Tools (SSDT) Project Templates (CTP 3.0)
• 4.11 Pin Report Items to a Power BI Dashboard (CTP 3.0)
• 4.12 Support of SharePoint mode and SharePoint 2016 (CTP 3.1)
• 4.13 SQL Server mobile reports hosted in Reporting Services available in Power BI Mobile app (CTP 3.2)
• 4.14 Mobile Report Publisher (CTP 3.2)
• 4.15 Reporting Services web portal (CTP 3.2)
• 5. Integration Services
• 5.1 AlwaysOn support (CTP 2.2)
• 5.2 New ssis_logreader database-level role in the SSIS catalog (CTP 2.3)
• 5.3 New RuntimeLineage logging level in the SSIS catalog (CTP 2.3)
• 5.4 New custom logging level in the SSIS catalog (CTP 2.3)
• 5.5 SSIS projects available in SQL Server Data Tools (CTP 2.3)
• 5.6 Column names for errors in the data flow (CTP 2.3)
• 5.7 Support for OData v4 data sources (CTP 2.3)
• 5.8 Improved experience for project upgrade (CTP 2.3)
• 5.9 Incremental package deployment (CTP 2.3)
• 5.10 Data Feed Publishing Components are now part of SSIS (CTP 2.4)
• 5.11 Balanced Data Distributor transformation is now part of SSIS (CTP 2.4)
• 5.12 Support for Excel 2013 data sources (CTP 2.4)
• 5.13 Upgrade blocked when SSISDB belongs to an Availability Group (CTP 3.0)
• 5.14 Support for Azure Blob Storage in the SQL Server Import and Export Wizard (CTP 3.0)
• 5.15 Support for the Hadoop file system (HDFS) (CTP 3.0)
• 5.16 Azure Feature Pack for SSIS released for SQL Server 2016 (CTP 3.0)
• 5.17 AutoAdjustBufferSize property automatically calculates buffer size for data flow (CTP 3.0)
• 5.18 Reusable control flow templates (CTP 3.0)
• 5.19 Improved performance for SSIS Catalog views (CTP 3.1)
• 5.20 Expanded support for Hadoop and HDFS (CTP 3.2)
• 6. Master Data Services
• 6.1 Improved Performance (CTP 2.0)
• 6.2 Improved Security (CTP 2.0)
• 6.3 Transaction Log Maintenance (CTP 2.0)
• 6.4 Improved Troubleshooting (CTP 2.0)
• 6.5 Improved Manageability (CTP 2.0)
• 6.6 Custom Indexes (CTP 2.2)
• 6.7 Entity Sync Relationship (CTP 2.2)
• 6.8 Member Revision History (CTP 2.2)
• 6.9 Manage Business Rules (MDS Add-in for Excel) (CTP 2.3)
• 6.10 Many-to-Many Relationships in Derived Hierarchies (CTP 2.3)
• 6.11 Merge Conflicts (CTP 2.3)
• 6.12 Business Rules Extension (CTP 2.4)
• 6.12 Attribute Filters (CTP 3.0)
• 6.13 Change Sets (CTP 3.0)
• 6.14 Purge Soft Deleted Members (CTP 3.2)
• 6.15 Change Set Email and Management (CTP 3.2)
• 6.16 Edit Many-to-Many Relationships in Derived Hierarchies (CTP 3.2)
• 6.17 View and Manage Revision History (CTP 3.2)
• 7. Query Execution
• 7.1 New XEvents (CTP 2.2)
• 7.2 Showplan xml is extended to include memory grant usage per thread and iterator (CTP 2.2)
• 8. Core Engine Scalability
• 8.1 Thread safe memory objects (of type CmemThread) will be dynamically promoted to be partitioned by NUMA node or by CPU (CTP 2.2)
• 9. DBCC CHECKDB improvements
• 9.1 Persisted computed columns and filtered indexes validation (CTP 2.2)
• 9.2 Performance improvements when validating a table with thousands of partitions (CTP 2.2)

0. Initial setup

0.1 Release notes

Release notes are:

• CTP 2.0 - SQL Server 2016 first public preview now available!
• CTP 2.1 - SQL Server 2016 Community Technology Preview 2.1 is available
• CTP 2.2 - SQL Server 2016 Community Technology Preview 2.2 is available
• CTP 2.3 - SQL Server 2016 Community Technology Preview 2.3 is available
• CTP 2.4 - SQL Server 2016 Community Technology Preview 2.4 is available
• CTP 3.0 - SQL Server 2016 Community Technology Preview 3.0 is available
• CTP 3.1 - SQL Server 2016 Community Technology Preview 3.1 is available
• CTP 3.2 - SQL Server 2016 CTP 3.2: Introducing end-to-end mobile BI
• CTP 3.3 - Technical Overview: SQL Server 2016 Community Technology Preview 3.3
• RC0 - Technical Overview: SQL Server 2016 Release Candidate 0
• RC1 - SQL Server 2016 Release Candidate 1 now available
• RC2 - SQL Server 2016 Release Candidate 2 now available
• RC3 - SQL Server 2016 Release Candidate 3 now available
• RTM - SQL Server 2016 is generally available today

0.2 Demo database setup

Demo database justdave setup

MD C:\DATA

CREATE DATABASE [justdave];

ALTER DATABASE [justdave] ADD FILEGROUP [justdave1] CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE [justdave] 
ADD FILE (name='justdave1_1', filename='c:\data\justdave1_1') 
TO FILEGROUP [justdave1];

USE [justdave];