What’s New with SQL Server 2014

I know you have been waiting for the next release of SQL Server.  Well you don’t have to wait any longer.  On June 25th Microsoft released the first community technical preview (CTP1) for the next version of SQL Server. 

In the past, when Microsoft released new versions of SQL Server they had code names, like Kilimanjaro, Denali, etc.   But for this release there is no clever code name.  Microsoft is just calling it SQL Server 2014.   In this article I will tell about the exciting new features that come along with SQL Server 2014.  Additionally I will let you know where you can download CTP1, as well as where you can find additional resources related to SQL Server 2014.

Noteworthy Enhancements

With this new release of SQL Server 2014 there are a number of new features that Microsoft has included.   Below I have outlined a number of those items I think are noteworthy.   By no means is this an exhaustive list of the new features that Microsoft has included in this release.

In Memory Built-in

The new In Memory Built-in support (code name Hekaton) has been included in SQL Server 2014.  This new memory management component has been designed to support OLTP applications, Data Warehouse column stores, as well as PowerPivot BI applications.  Microsoft claims that In Memory Built-in will improve performance by as much as 10 times, and improves scalability by 2-5 times.  It does this be minimizing the latency it takes to retrieve data. This feature will be great for applications that need performance, have relatively short transactions, have a high degree of concurrent transactions with many connections, etc.

It is relatively easy to create table and/or stored procedures to take advantage of the In Memory Built-in feature. Only minor changes to the way you code your CREATE TABLE and CREATE PROC statements are required.  Changes have been incorporated into the database engine, so In Memory Built-In objects are processed to take advantage of memory processing, instead of using the b-tree indexes and buffer pools.

Extending Buffer Pool Memory to Solid State Drives

SQL Server 2014 will now allow you to extend the memory SQL Server uses by using nonvolatile random access memory (aka Solid State Drives) to greatly improve I/O throughput. This feature is designed to help offload the small random I/Os that SQL Server has traditionally done to mechanical disks, to Solid State Drives.  This feature is currently targeted for only being available to those that purchase Enterprise 64 bit version of SQL Server 2014.

Enhanced Scalability

Microsoft has modified SQL Server 2014 to increase the upper limits to extend scalability.  SQL Server 2014 has increased the number of logical processors to 640 and memory up to 4 TBs.  Additionally SQL Server running in a virtual environment can support up to 64 virtual processors, and 1 TB of memory. If you want to have a clustered SQL Server environment SQL Server 2014 will support up to 64 different nodes.  Resource governor has also been improved with SQL Server 2014.  With this new release you can now constrain resource pools based IOPS per volume.

Higher Availability

SQL Server AlwaysOn became available in SQL Server 2012.  With the rollout of SQL Server 2014 the AlwaysOn feature has been enhanced.  Additionally, the number of secondary nodes have been increased from 4 to 8.   

Query Handling Enhancements

With SQL Server 2014 the development team built a new cardinality estimator.  The new estimator should provide more consistent and predictable query performance, as well as more accurate cardinality estimates so better query plans can be chosen.

For a while SQL Server has had a method to automatically update statistics when the database engine feels they are out of date.  With the introduction of SQL Server 2014 Microsoft has made enhancements to the algorithm that determines when statistics are out of date.  This means that statistics will be invalidated sooner, which will lead to the triggering of the update statistics process. By updating statistics more often SQL Server will be able to create better optimized query plans. 

Microsoft has increased the performance of the SELECT INTO statement.  They have done this by allowing the SELECT INTO to perform parallel operations when inserting rows into a table.

Updatable Columnstore Index

Microsoft introduced the non-clustered columnstore index in SQL Server 2012.  One of the limitations you had, once you created a non-clustered columnstore index on a table in SQL Server 2012, was you couldn’t perform an INSERT, UPDATE, or DELETE statement against the table without first dropping the index.  This update limitation has now been lifted in SQL Server 2014.  Microsoft did this be creating a true clustered columnstore index.

New Security Features

With the introduction of SQL Server 2014 Microsoft has introduced some new security permissions that allow you to now setup server level roles that provide only the access that administrators need to perform their duties.  Three new server level permissions are being introduced in SQL Server 2014 to help you with establishing these new server roles to allow for separation of duties.  These new server permissions are:

  • CONNECT ANY DATABASE
  • IMPERSONATE ANY LOGIN
  • SELECT ALL USER SECURABLES

These new permissions will now allow you to set up permissions so database administrators can perform their duties but not see the underlying user data.  Additionally, you can use these server permissions to allow your middle tier applications to impersonate any logon except those with high-level permissions like sa, or sysadmin

Where to Download CTP1 and Find Additional Resources

If you haven’t already downloaded SQL Server 2014 CTP1 you can download it from here:

http://technet.microsoft.com/en-us/evalcenter/dn205290.aspx

You can also find some additional white papers about SQL Server 2014 by checking out these links:

Installing CTP1

You will find that when you try to install CTP1 you cannot install it on a machine that is already running SQL Server.  You will need a fresh machine, or virtual machine to install SQL Server 2014.   Typically when Microsoft releases its first CTP it hasn’t done complete testing, and integration with other versions of SQL Server.  I’m sure by the time Microsoft releases SQL Server 2014 this limitation will no longer exist.

Product Documentation

Books Online documentation has not been incorporated into CTP1.  You can get to the Books Online documentation via the product documentation page that can be found here: http://msdn.microsoft.com/en-us/library/bb418471(v=sql.10).aspx .  As of the writing of this article the Books Online documentation found by using this link does not contain a complete set of reference documentation for SQL Server 2014.

Additionally, you can use the link below to get to the SQL Server 2014 Transact-SQL Reference material: http://msdn.microsoft.com/en-us/library/bb510741(v=sql.120).aspx

Features by Edition

Like other releases of SQL Server, Microsoft is not making all of the new features available in all editions of SQL Server 2014.  If you are interested in which features will be available by edition you should review this link:

http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx

The Next Generation

Microsoft keeps enhancing SQL Server.  With this next generation, SQL Server 2014 offers many new exciting enhancements, and features.  I’ve only touched on some of the new features I think are noteworthy.  There are many other additional features packed into SQL Server 2014.  If you haven’t looked into what SQL Server 2014 has to offer yet, then you should at least download the white papers and look at the other reference material available.  If you have more time I’d suggest you install CTP1 and start testing out the new features it has to offer.

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles