SQL Server 2014 Features

Hi All,

I am listing SQL Server  2014 top 10 new features here.

Microsoft introduced some significant enhancements inSQL Server 2014—especially with In-Memory OLTP. However, as you might expect after such a short release cycle, not every subsystem has been updated; there are no major changes to SQL Server Integration Services (SSIS), SQL Server Replication Services, or SQL Server Reporting Services (SSRS). Nonetheless, there are plenty of significant enhancements. Here are 10 new features in SQL Server 2014.

1. In-Memory OLTP Engine

SQL Server 2014 enables memory optimization of selected tables and stored procedures. The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control  mechanism to eliminate locking delays. Microsoft states that customers can expect performance to be up to 20 times better than with SQL Server 2012 when using this new feature. For more information, check out “Rev Up Application Performance with the In-Memory OLTP Engine.”

2. AlwaysOn Enhancements

Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now also available for read workloads, even when the primary replica is unavailable. In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create asynchronous secondary replicas in Windows Azure.

Related: No Fooling: SQL Server 2014 to Release April 1

3. Buffer Pool Extension

SQL Server 2014 provides a new solid state disk (SSD) integration capability that lets you use SSDs to expand the SQL Server 2014 Buffer Pool as nonvolatile RAM (NvRAM). With the new Buffer Pool Extensions feature, you can use SSD drives to expand the buffer pool in systems that have maxed out their memory. Buffer Pool Extensions can provide performance gains for read-heavy OLTP workloads.

4. Updateable Columnstore Indexes

When Microsoft introduced the columnstore index in SQL Server 2012, it provided improved performance for data warehousing queries. For some queries, the columnstore indexes provided a tenfold performance improvement. However, to utilize the columnstore index, the underlying table had to be read-only. SQL Server 2014 eliminates this restriction with the new updateable Columnstore Index. The SQL Server 2014 Columnstore Index must use all the columns in the table and can’t be combined with other indexes.

5. Storage I/O control

The Resource Governor lets you limit the amount of CPU and memory that a given workload can consume. SQL Server 2014 extends the reach of the Resource Governor to manage storage I/O usage as well. The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool.

Related: A Tale About SQL Service Packs for 2014

6. Power View for Multidimensional Models

Power View used to be limited to tabular data. However, with SQL Server 2014, Power View can now be used with multidimensional models (OLAP cubes) and can create a variety of data visualizations including tables, matrices, bubble charts, and geographical maps. Power View multidimensional models also support queries using Data Analysis Expressions (DAX).

7. Power BI for Office 365 Integration

Power BI for Office 365 is a cloud-based business intelligence (BI) solution that provides data navigation and visualization capabilities. Power BI for Office 365 includes Power Query (formerly code-named Data Explorer), Power Map (formerly code-named GeoFlow), Power Pivot, and Power View. You can learn more about Power BI atMicrosoft’s Power BI for Office 365 site.

8. SQL Server Data Tools for Business Intelligence

The new SQL Server Data Tools for BI (SSDT-BI) is used to create SQL Server Analysis Services (SSAS) models, SSRS reports, and SSIS packages. The new SSDT-BI supports SSAS and SSRS for SQL Server 2014 and earlier, but SSIS projects are limited to SQL Server 2014. In the pre-release version of SQL Server 2014, SQL Server Setup doesn’t install SSDT-BI. Instead, you must download SSDT-BI separately from the Microsoft Download Center.

9. Backup Encryption

One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.

10. SQL Server Managed Backup to Windows Azure

SQL Server 2014’s native backup supports Windows Azure integration. Although I’m not entirely convinced that I would want to depend on an Internet connection to restore my backups, on-premises SQL Server 2014 and Windows Azure virtual machine (VM) instances support backing up to Windows Azure storage. The Windows Azure backup integration is also fully built into SQL Server Management Studio (SSMS).

SQL SERVER 2016 Features

SQL SERVER -- SQL SERVER 2016 WHAT'S NEW ?

1      Query Store

a)      Captures queries, query plans, runtime statistics, etc.

b)      Can enforce polices to direct SQL Server Query processor to execute in specific manner

c)      Contains query work load history

d)      Helps system-wide or database level performance analysis and troubleshooting

e)      New system views are added to support Query store

2      Live Query Statistics

a)      Live Query plans with system resources consumption such as CPU, memory for all running queries in the plan

b)      You can drill down further into live running queries from its query plan

c)      Helps you watch change of statistics during query execution

3      Native JSON Support

a)      Language - Independent data-interchange format

b)      Modern Web and Mobile Applications are using it

c)      Alternate XML

d)      Parsing done via Standard JavaScript function

e)      Easier and faster than XML parsing

f)       Several new T-SQL constructs introduced to facilitate JSON

4     Temporal Database support

a)      Keep track data in the database (Current and historical)

b)      Provides ability to query the table snapshot for any point in time

c)      Data can be restored to any known state without downtime

d)      Help regulate compliance and audit

e)      Help implementing slowly changing dimension

5      Always Encrypted

a)      Encrypted data at rest and in motion

b)      Encryption master key resides at Application

c)      No application change required (Simple SSIS Package convergence)

6      Row Level Security

a)      Security can be defined on a complete row

b)      

7      Column Store

a)     Column store index on your in-memory table for higher throughput

b)     

8      Polybase

a) Querying relational and non-relational data

b) Example: SQL + Hadoop (With the help of external table)

9    Advance Analytics

a)      Data Scientists can get model or templates from MS Azure where others are sharing and    

hook it up with their real time data for data examining

10    BI on Mobile Devices

a)      All BI on Mobile: (New) – DataZen App

                                               I.          Windows

                                             II.          iOS

                                            III.          Android

                                            IV.          HTML5

11   Data Stretch to MS Azure

a)      Stretch the data to the cloud (Turn the feature on and it works with Always Encrypted)

a.      Remote data archive (DBA)

b.      Stretch tables into Azure

c.      Power BI Scenarios with SSIS

b)      Enhanced Backup/restore for stretched databases

12   SSDT (All in One)

SQL Server 2016 Enhancements from SQL Server 2014

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

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