Advertisement

Latest Post

Wednesday, 3 May 2017

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




Government Jobs