SQL Server 2016 Enhancements from SQL Server 2014

Here is the summary for enhanced features of SQL Server 2016 from SQL Server 2014.

SQL SERVER 2016

Enhancements?

1-    Performance

a)     In-memory OLTP

                                     I.          Altering in memory table is now allowed (2014, you needed to recreate the table)

                                   II.          More size of durable tables

                                  III.          Migration of tables and stored procedures to in-memory report is added in SSMS standard reports and doesn’t require management data warehouse any more

b)     Query Processing

                                     I.          Better Query plan choices and more accurate cardinality estimates

                                   II.          Faster and more frequent Stats updates are possible

                                  III.          Parallel insertion is possible with select into operation

c)      Data and backup compression

                                     I.          Compression level has increased for database as well as backup

d)     Data collector

                                     I.          Improved DMVs, SQL Traces, Server activity, disk usage, and query usage

                                   II.          Improved database tuning advisor (Indexes, views and partitions can be created using database tuning advisor)

                                  III.          Performance reports are added in standard reports

2-    Security

a)     Transparent Data Encryption (TDE)

                                     I.          TDE now supports storage of memory optimized tables

                                   II.          Tables and column level masking is improved

b)     Backup Encryption

                                     I.          Backup encryption is now supported with compression

c)      SQL Server Audit Tools

                                     I.          Underlying operation can be audited now besides just permission audit

3-    Availability

a)     Availability of Mission Critical systems

                                     I.          Load balancing between Primary and Secondary replica

                                   II.          Auto Failover from 2 to 3

                                  III.          DTC tied to database instead of instance

                                  IV.          AG health enhanced to database level instead of only instance level

b)     Online database Operations

                                     I.          No blocking for alter and truncate operations (100% uptime for tables during these operations)

c)      Data backup enhancement

                                     I.          Enhanced GUI for backup/restore operation

4-    Scalability

a)     Windows and SQL Server enhancements

                                     I.          Supported on Windows server Core (less patches, less maintenance and less downtime)

                                   II.          Support for windows server ReFS(Resilient File System)

                                  III.          Faster live migration for SQL VM

                                  IV.          Cluster-Aware Updating improved

                                   V.          Dynamic Quorum

b)     Buffer Pool Extension

                                     I.          Buffer pool can now use SSDs (Solid-state Drives)

c)      Partitioning

                                     I.          Scale upto 1500 table partitions (helps to deal with Large data sets which applications such as SAP uses daily or hourly)

d)     Distributed Replay

                                     I.          Multithreaded replay is introduced as opposed to single threaded replay for production get ready planning

e)     Tempdb Optimization

                                     I.          Multiple TempDB files per instance for multi-core environments 

SQL Server 2016 Enhancements from SQL Server 2014