What’s New (that matters) In SQL 2014

Microsoft’s release of SQL 2014 might seem like it was an April fool’s joke, but they were quite serious with their general release of SQL 2014 on April 1st 2014.  While there are many high-availability enhancements, and OLTP changes for in-memory operations (all of which are interesting), I would like to highlight some of the more important Data Warehousing and BI improvements that make this a compelling upgrade, so this is the list “that matters” – to me.

Incremental statistics

Whenever statistics need to be rebuilt, you can’t just update the new items, the statistics for the whole table or index need to be updated. This means that a table many millions of rows with a small percentage of change, you would need to update statistics on the whole thing.  In SQL Server 2014, statistics can be updated on specifically on the data that has changed, and merged with the existing statistics.  Particularly in large Data Warehouse implementations, this approach will significantly reduce the impact of maintenance work

Updateable Column Store Index

Columstore indexes were introduced in SQL Server 2012, however were not updateable which proved to be problematic for large tables (where the best performance increases would be seen).  In SQL Server 2014, the Columnstore indexes are now updateable, which is a major improvement for large Data Warehouse tables.

Resource Governor – Adds I/O and Storage workload management

Originally, the Resource Governor allowed control over CPU and memory allocations for the defined workload groups.  This was useful in “penning in” applications that would overrun the server, and provide a more constant delivery response. ( Resource Governor Does it Work?) New with this release, the Resource Governor provides a new capability to manage application storage I/O utilization, you can now manage storage I/O usage as well. The Resource Governor can now limit the physical I/O’s issued for user threads in a given resource pool, allowing you to have an even more predictable query performance.

Lock priority of online operations

Also to reduce the impact of maintenance work, particularly for very large Data Warehouse tables, you can now specify a lock priority for online re-indexing. Previously,   long running queries could block re-indexing operations, which would then impact the ability to actually get the index rebuilt. In SQL Server 2014, you can specify how your re-index operation will handle being blocked. You specify how long it will wait and what to do when the wait is over. Will you have it follow traditional behavior and wait indefinitely.  You can have it terminate and move to the next table. Or you can select to kill the blocking query, so your re-indexing can complete.

Delayed durability

In SQL Server, changes to data are written to the log first. This is called write ahead logging (WAL). Control isn’t returned to the application until the log record has been written to disk (a process referred to as “hardening”). Delayed durability allows you to return control back to the application before the log is hardened. This can speed up transactions if you have issues with log performance. You do, however, sacrifice recoverability; should the database go down before the log is committed to disk, then you lose those transactions forever. It may be worth the risk if your log performance is severely degrading application response times, or in Data Warehousing applications where there are large (recoverable from the source) blocks of writes that are occurring.

Buffer Pool storage on SSD

A new solid state disk (SSD) integration capability that enables you to use SSD storage to expand SQL Server 2014’s buffer pool. The new buffer pool enhancements can help increase performance in systems that have maxed out their memory capability by using high-speed nonvolatile RAM (NVRAM) in the SSD drives as an extension to SQL Server 2014’s standard buffer pool.

Enhanced Windows Server 2012 Integration

These new enhancements will allow for better (larger) horizontal scaling on a single server before requiring additional server hardware.  The improved integration with Windows Server 2012 R2 and Windows Server 2012, SQL Server 2014 will have the ability to scale up to 640 logical processors and 4TB of memory in a physical environment. It can scale up to 64 virtual processors and 1TB of memory when running on a virtual machine (VM).  Additionally, with the “stoarage spaces” feature of the OS, SQL server can now access pools of tiered storage.

Enhancements to Backups

Database backups in SQL Server now support built-in database encryption.  The backup encryption process uses either a certificate or an asymmetric key to encrypt the data. The supported backup encryption algorithms are Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES (3DES).  (There is a discontinued feature of backing up “with password”)

Unchanged Subsystems and Discontinued Features

There are no significant SQL Server engine features that are being discontinued with this release, list is here: http://msdn.microsoft.com/en-us/library/ms143729(v=sql.120).aspx

Power BI – Office 365 Integration

Power BI for Office 365 is Microsoft’s cloud-based BI solution that leverages familiar Office 365 and Excel tools (most of which will require Excel 2013). Power BI for Office 365 provides business insights through data visualization and navigation capabilities.

• Power Pivot (formerly PowerPivot). This Excel 2010 and Excel 2013 ProPlus add-in enables Excel to perform data analysis on large volumes of data.
• Power View. This Excel 2013 ProPlus add-in provides a Silverlight-based data visualization and navigation tool. Microsoft has extended Power View so that you can now use it with multidimensional models (OLAP cubes). Power View multidimensional models also support queries using Data Analysis Expressions (DAX). Power View’s data visualization capabilities have also been enhanced. Power View now supports a number of data visualizations, including tables, matrixes, bubble charts, and geographical maps.
• Power Query (formerly code-named Data Explorer). This Excel 2013 add-in lets you discover and integrate data into Excel. It supports SQL Server data sources as well as external sources such as Windows Azure, text files, XML files, Open Data Protocol (OData) feeds, web pages, Hadoop data sets, and public data sets (e.g., Open Government data from data.gov).
• Power Map (formerly code-named GeoFlow). This Excel 2013 ProPlus add-in provides 3D mapping visualizations.