SQL Server 2014



Eron Kelly, General Manager for Microsoft SQL Server Marketing, announced about the release of a preview of SQL Server 2014 by the end of June 2013 and Microsoft Corporate Vice President Brad Anderson announced that the SQL Server 2014 will be available in early 2014. As expected, the new SQL Server 2014 release will provide several significant new features.

Let’s have a look at the expected features of SQL Server 2014

1.     Hekaton - In-memory OLTP database capability for transactional applications

This is supposed to be the most exciting feature of SQL Server 2014. This is primarily for those companies who have applications that are having specific locking and blocking issues on the super high I/O systems. Though some code changes may be necessary to get the most out of the in-memory feature of SQL Server 2014, the greatest benefit is that it comes as part of the normal SQL Server database engine. This will be an optional setting for database administrators, who can choose which database instances or tables to put into memory. There will be a diagnostic tool that will suggest which databases and tables are the best candidates to run in-memory. It is said that the "Hekaton" codename will fade, and that it will be referred to as the SQL Server In-Memory OLTP Engine.

 

Hekaton also looks for stored procedures, or queries that are stored for regular use that would be ripe for optimization as well. The software can identify heavily used stored procedures and can compile them to machine code. Machines code typically executes much more quickly—and requires less memory—than code written in high-level languages such as SQL itself.

 

Another way Hekaton improves performance is by refining the process of locking database tables to write data. Traditionally, in order to avoid overwriting of data mid transaction by another operation, a RDMS will lock a table page each time a row of data needs to be updated. Multiple concurrent reads or writes to a database table can, however, create a backlog, slowing performance. The OLTP Engine doesn’t lock data by pages. So each process can hit any row it wants, and the only time there is a conflict is when two processes hit the same row at the exact same instant. Even individual rows aren’t locked when they are being written to. Rather, the RDMS writes the updated row to a new location and leaves a pointer to the new location in the old row, where it can be followed by any query that takes place at the same time as the update. This technique is called “optimistic concurrency.”

 

2.     xVelocity - Column-store indexes feature of SQL Server 2014

This will allow continuous loading of data. Column-store indexes can speed up query times by storing columns in a different way than traditional indexes, which basically allows SQL Server to compress the data in the index. But in SQL Server 2012, once you convert tables to column-based, the data in the indexes is static. With SQL Server 2014, DBAs will be able to load and delete data in existing column-store indexes

 

3.     Smart and Quick Database Backup to Azure

In SQL Server 2014, DBAs will be able to right-click in SQL Server Management Studio and create secondary and tertiary database backups in Azure for disaster recovery.

 

4.     Cache frequently used data on Solid State Disks (SSDs)

SQL Server 2014 will automatically cache active data in SSD with zero risk of data loss, and then put others on traditional disks if they contain "cold" data. The best use case is for read-heavy OLTP workloads. SSDs can be useful to you if

·         Your total actively queried data is bigger than you can fit in memory or

·         You already maxed out of the memory on the server or

·         Your business requirements force you to use shared storage or magnetic local storage or

·         Your server has room for locally attached PCI Express or SAS/SATA solid state drives.

 

5.     More online maintenance operations

If your big data is in partitioned table and you are running short of time for maintenance, with SQL Server 2014, you can rebuild a single partition’s index online, and you can switch partitions in/out using DBA-specified lock priorities. For 24/7 workloads, this gives the DBA the ability to do maintenance with lower locking, CPU, and memory overhead. There’s also new Extended Events stuff you can use to monitor who is getting blocked and killed.

 

6.     BI Improvements

In the SQL Server 2014 release, Data Explorer will provide new data visualizations, GEOFlow will provide better visual mapping and Power View will be able to work against multi-dimensional models in addition to tabular data models.

 

7.     SQL Server AlwaysOn

With SQL Server 2012 Microsoft released the first implementation of AlwaysOn, mainly a completely new High Availability technology based on the principles of Database Mirroring. With the SQL Server 2014 release Microsoft now supports up to 8 different Secondaries, which provides you a huge scale out in comparison of the 4 Secondaries that are possible currently with SQL Server 2012. Of course, you’ll be paying for Enterprise Edition licensing on these, but if you were already going to replicate data out to various reporting or BI servers, now your life is easier.

Other cool improvements:

 

·         Query performance improvements due to better cardinality estimator

·         Resource Governor for IO

·         Sysprep enhancements

·         Wizard to deploy database to Azure VM

·         Separation of duties enhancements to support DBAs who aren’t allowed to read the data, or auditors who are allowed to read the data but not manage the server

·         Windows Server 2012 R2 cooperation improvements – ReFS support, online resize VHDX, tiered storage, SMB improvements.

 

You can get notified about the release of the trial version of the SQL Server 2014 from http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx