Important New Features in SQL Server 2014

What's new in SQL Server 2014? SQL Server 2014 New Features | Important New Features in SQL Server 2014

Let's take a look at the new features and improvements with SQL Server 2014.

Power BI for Office 365 Integration

• Power Pivot (formerly PowerPivot).
• Power View
• Power Query (formerly code-named Data Explorer)
• Power Map (formerly code-named GeoFlow)

In-Memory OLTP Engine

The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays.

SQL Server 2014 enables memory optimization of selected tables and stored procedures.

In-Memory OLTP has the following characteristics:

• Only hash and range indexes are available;
• No buffer pool;
• Stream-based storage for durability;
• Full ACID support;
• Core engine uses lock-free algorithms;
• No lock manager, latches or spinlocks;
• Use of T-SQL compiled to machine code, via C code generator (C compiler integrated in SQL Server 2014 engine);
• Invoking procedure is just a DLL entry-point;
• Aggressive optimization at compile-time.

SQL Server Data Tools for Business Intelligence

The new SSDT-BI supports SSAS and SSRS for SQL Server 2014 and earlier, but SSIS projects are limited to SQL Server 2014.

SQL Server AlwaysOn Improvements

Microsoft has enhanced AlwaysOn integration. In Sql Server 2014, the maximum number of secondary replicas is expanding from four to eight.

Even when the primary replica is unavailable, it provide readable secondary replicas for read workloads.

SQL Server 2014 also provides the new Add Azure Replica Wizard. With helps of this, we can create asynchronous secondary replicas in Windows Azure.

SQL Server Transactions with Delayed Durability

SQL Server Query Optimizer

SQL Server 2014 substantially improved the component of the engine that creates and optimizes query plans.

SQL Server Table and Index Operations

Sql Server 2014 provided very powerful feature to enhance performance. By using this feature we can rebuild single partitions or we can switch partition and index rebuild operations can be performed while the table is online.

To manage lock priority of online operations for tables and indexes has been added that allowing us to use WAIT_AT_LOW_PRIORITY option.

By use of this option, you can define operation locks time maximum duration and abort conditions.

Argument	Description
MAX_DURATION	Is the wait time in minutes the online operation will wait at low priority
ABORT_AFTER_WAIT	These are the actions to be taken by the online operation when it is blocked beyond MAX_DURATION value. NONE: Continue waiting for the lock with normal priority. SELF: Leaves current operation without taking any action. BLOCKERS: Kills transactions that block the online operation.

Here is an example.

ALTER INDEX ALL ON yourTable REBUILD

 WITH (ONLINE = ON (

   WAIT_AT_LOW_PRIORITY (

    MAX_DURATION = 4

    MINUTES ABORT_AFTER_WAIT = BLOCKERS

      )

    )

  )

Incremental Option for CREATE STATISTICS

This feature permits statistics creation per partition by setting the INCREMENTAL option to ON in the CREATE STATISTICS statement.

 Here is an example.

CREATE STATISTICS yourStatistic

ON dbo.yourtable

 (

    tableField

 )

WITH FULLSCAN, INCREMENTAL =  ON;

GO

Buffer Pool Extension

Buffer Pool Extension feature is provided in sql server 2014 for performance enhancements. This feature enables SQL Server to be configured to use a SSD disk as an extension for the Database Buffer Pool in order to reduce response time.

Resource Governor

We can set constraints on the physical I/O operations.

In ALTER RESOURCE GOVERNOR statement, The MAX_OUTSTANDING_IO_PER_VOLUME argument has been added to allow you to set the maximum outstanding I/O operations per disk volume according to the disk IO characteristics.

The two options MAX_IOPS_PER_VOLUME and MIN_IOPS_PER_VOLUME can be used to set the maximum and minimum IO operations per second respectively.

Columnstore Index Improvements

Updatable Clustered Columnstore Indexes has been added in SQL Server 2014. To handle Columnstore Indexes compression, two arguments have been added to the REBUILD option of the ALTER INDEX statement.

This batch mode improves CPU utilization by:

• Minimizing instructions per row
• Taking advantage of cache structures
• Highly efficient algorithms
• A better parallelism.

Argument	Description
COLUMNSTORE_ARCHIVE	Compress Columnstore indexes even more.
COLUMNSTORE	Default Columnstore indexes Compression.

Security Enhancements   

1:- Backup Data Encryption

Sql Server 2014 supports several new encryption algorithms like AES 128, AES 192, AES 256 and 3DES that can be used to perform encryption on backup data during the backup creation.

The supported encryptions are:

·         AES 128;

·         AES 192;

·         AES 256;

·         Triple DES.

The supported encryptors are:

·         A Certificate;

·         An Asymmetric Key.

2:- Permission Changes in SQL Server 2014

Following permission has been added in SQL Server 2014:

Permission	Description
CONNECT ANY DATABASE	is a server level permissions which allows to connect all current databases and any new databases that might be created in the future
IMPERSONATE ANY LOGIN	is a server level permissions which allows a middle-tier process to impersonate the account of clients connecting to it, as it connects to databases
SELECT ALL USER SECURABLES	is a server level permission which allows to view data in all databases that the login has the permission to connect to
ALTER ANY DATABASE EVENT SESSION	is a database level permission which allows a role to read all metadata