Essential Guide

SQL 2014: Investigating Microsoft's latest database release

A comprehensive collection of articles, videos and more, hand-picked by our editors

Eight key SQL Server 2014 features

In-memory OLTP and columnar storage are just two of the eight key SQL Server 2014 features, according to this tip.

Microsoft Corporate Vice President Quentin Clark announced the second Community Technology Preview (CTP2) of SQL Server 2014 at the PASS Summit 2013 last fall. This CTP2 release allows you to test and try every single new feature of SQL Server 2014, which is expected to be launched in the first half of 2014. Here is a breakdown of eight key new SQL Server 2014 features.

Enhanced In-Memory OLTP engine

The SQL Server 2014 In-Memory online transaction processing (OLTP) engine, previously code-named "Hekaton," allows you to create in-memory optimized OLTP tables within a conventional relational database. The In-Memory OLTP engine solves problems in high-concurrency situations, as it uses data structures that are entirely latch-free (lock-free), meaning there are no latches or spinlocks on performance-critical paths in the system. Instead, it uses an optimistic multi-version concurrency control (MVCC) technique that provides transaction isolation semantics, which helps to avoid interference among transactions. Thus, any user process can access any row in a table without acquiring latches or locks. The combination of this MVCC and latch-free data structures results in a system in which the user processes can run without stalling or waiting. In addition, stored procedures operating on optimized memory tables, though written in Transact-SQL, are actually compiled to highly efficient machine code. This maximizes the runtime performance for certain workloads and certain types of queries, because the generated machine code only contains exactly what is needed to run the request, and nothing more. According to Microsoft, some applications can achieve a 50x performance increase simply by using the In-Memory OTLP engine.

The In-Memory OLTP feature is only supported on 64-bit Enterprise, Developer or Evaluation editions of SQL Server 2014. To start using In-Memory OLTP, which is arguably the biggest of SQL Server 2014 features, review the Microsoft TechNet resource on the topic.

You can use the Memory Optimization Advisor wizard, which can be launched from SQL Server Management Studio (SSMS), to help identify and migrate fully compatible tables in memory, and select the stored procedures that can be compiled into machine code for high-performance execution.

Enhanced In-Memory columnstore indexes

With the release of SQL Server 2012, Microsoft introduced a new type of nonclustered index called the xVelocity memory optimized columnstore index. But SQL Server 2012 columnstore indexes are not updateable, meaning that once the column store index is created, you cannot directly add, delete or modify data in the underlying table. In order to update the underlying table information, the index needed to be dropped or disabled first and then recreated once the data is updated. SQL Server 2014 improves columnstore indexes and has both clustered and nonclustered columnstore indexes, with both types of indexes being updateable.

Buffer pool extension

The buffer pool extension feature provides each SQL Server node the ability to have its own solid-state drives (SSD) as a non-volatile random access memory for buffering. This is the server-level configuration that allows a buffer pool to accommodate larger OLTP workloads. This helps to resolve I/O bottlenecks while improving overall I/O throughput, due to lower latency and better random I/O performance of SSDs. In addition, it guarantees no risk of data loss, as it only deals with clean pages.

The following is the typical syntax to enable this feature:

ALTER SERVER CONFIGURATION
 SET BUFFER POOL EXTENSION
 { ON ( FILENAME = 'os_file_path_and_name'
     ,SIZE = <size> [ KB | MB | GB ] )
 | OFF }

The buffer pool extension feature is only supported on 64-bit Enterprise, Developer or Evaluation editions of SQL Server 2014.

IO Resource Governor

Another of the top SQL Server 2014 features introduces two new settings for Resource Governor, which can be used to control the physical I/Os issued for user threads of a given resource pool. These new settings are: MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME. These settings are minimum and maximum physical I/O operations per second per disk volume for a resource pool. These new settings can help one to predict and physical I/O operations for their most critical workload. For more information, see ALTER RESOURCE GOVERNOR (Transact-SQL).

AlwaysOn enhancements

SQL Server 2014 also integrates AlwaysOn Availability Groups with Windows Azure VM, meaning we can now add Windows Azure VM as asynchronous Availability Group replicas. In addition, SQL Server 2014 now supports up to eight secondary replicas that remain available for read workloads, even when disconnected from the primary replica. AlwaysOn Availability Groups also supports the In-Memory OLTP feature.

Windows Azure integrated backup

SSMS in SQL Server 2014 enables you to directly back up on-premises SQL Server databases to Windows Azure storage. You can also restore databases from Windows Azure storage.

Security enhancements

Microsoft SQL Server 2014 also introduces some new security permissions. These new server permissions are: CONNECT ANY DATABASE, IMPERSONATE ANY LOGIN and SELECT ALL USER SECURABLES. These new permissions allow you to set permissions for database administrators so that they can only carry out their duties, but does not see the underlying user data.

Encryption for backups

SQL Server 2014 has the ability to encrypt data within the database during a backup operation.

This was first published in January 2014

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

SQL 2014: Investigating Microsoft's latest database release
Related Discussions

Basit Farooq asks:

What is your favorite of the top SQL Server 2014 features?

1  Response So Far

Join the Discussion

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close