Good news, Microsoft release SQL Server 2014 recently. SQL Server 2014 has several compelling new features that can definitely justify an upgrade. For OLTP, the most exciting new feature in SQL Server 2014 is In-Memory OLTP (aka "Hekaton"), which allows you to move individual tables to special in-memory structures. The performance boost can be as huge as 30x.
New In-Memory OLTP Engine
SQL Server 2014 enables memory optimization of selected tables and stored procedures. The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays. Microsoft states that customers can expect performance to be up to 20 times better than with SQL Server 2012 when using this new feature.
Enhanced Windows Server 2012 Integration
SQL Server 2014 provides 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).
SQL Server 2014 also integrates with several new and improved features in Windows Server 2012 R2 and Windows Server 2012. For example, SQL Server 2014 supports the OSs' new Storage Spaces feature. With Storage Spaces, you can create pools of tiered storage to improve application availability and performance. In addition, SQL Server 2014 can take advantage of the OSs' Server Message Block (SMB) 3.0 enhancements to achieve high-performance database storage on Windows Server 2012 R2 and Windows Server 2012 file shares. Many enhancements were made to SMB 3.0, with the most notable being SMB Transparent Failover and SMB Direct. The new SMB Transparent Failover feature provides highly reliable SMB storage that's fully supported for applications like SQL Server and Hyper-V. With the new SMB Direct feature, you can leverage the NIC's Remote Direct Memory Access (RDMA) feature to provide access speeds for SMB file shares nearing the access speed for local resources.
Enhancements to AlwaysOn Availability Groups
AlwaysOn Availability Groups provides an effective solution for high availability for SQL Server 2012 / 2014 in on-premises deployments as well as for Windows Azure VM deployments. SQL Server 2014 also contains multiple enhancements for AlwaysOn Availability Groups. The maximum number of secondary replicas is increased from 4 to 8. When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads. There are also additional supportability enhancements. Note that another enhancement is the Add Azure Replica Wizard. This will not be covered, since it requires VPN hardware etc. that is currently not easily available.
Updateable Columnstore Indexes
Things are changing now with SQL Server 2014, because Microsoft has resolved the above mentioned issues – with some magic and illusion: SQL Server 2014 provides you an Updateable Clustered ColumnStore Index! Let’s have a more detailed look on how this magic and illusion happens internally in SQL Server.
The first most important fact is that an underlying direct update of a ColumnStore Index is not possible! It would be too time consuming to do the complete decompress and compress on the fly during your INSERT, UPDATE, and DELETE transactions. Therefore SQL Server 2014 uses help from some magic: Delta Stores and Delete Bitmaps. Let’s have a more detailed look on both concepts.
SQL Server Data Tools for Business Intelligence
The BI aspects of SQL Server have also been improved. Data Tools for Business Intelligence are used to create SQL Server Analysis Services (SSAS) models, SQL Server Reporting Services (SSRS) reports, and SQL Server Integration Services (SSIS) packages. SSDT BI is based on Microsoft Visual Studio 2012. The database also supports Power BI for Office 365, with Power Pivot and Power View for data analysis and visualization.
Power View has been extended to provide support for multidimensional models, and for queries written using Data Analysis Expressions (DAX). A new Excel add-in, Power Query, lets you integrate data into Excel, and Power Map provides 3D mapping.
Why Should Upgrade?
Upgrading to a new release is usually a difficult decision for most organizations. However, SQL Server 2014's new In-Memory OLTP engine, with its promise of significantly improved application performance, offers a very compelling reason to upgrade for customers using SQL Server to support OLTP applications. One great way to find out the type of performance improvement that you might get out of SQL Server 2014's In-Memory OLTP capability is to download and install the SQL Server 2014 Evolution Edition and use the AMR tool to analyze your production workload. The AMR tool supports collecting data on SQL Server 2008 and later instances. This will give you a good idea of the type of performance improvements that you might expect to get using the new In-Memory OLTP engine as well as the changes that you might need to make to implement it. SQL Server 2014's In-Memory OLTP support promises to boost your database application performance to the next level