Infrastructure at your Service

Nathan Courtine

SQL Server 2014: An overview of the New Features

The Microsoft Techdays 2013 took place on 12-13 November at the Congress Center in Basel and on 18-19 November at the Beaulieu Congress Center in Lausanne. With more than 400 participants in Lausanne and around 1’000 participants in Basel, the largest technology event Microsoft in Switzerland was a great success. You can follow all the presentations from Lausanne or Basel. In this blog, I will summarize the presentation of SQL Server 2014: les nouveautés by Tien Dung Thoi at the TechDays in Lausanne.

In-Memory OLTP

The main new feature in SQL Server 2014 is In-Memory OLTP, which grants you a significant speed improvement. This flagship feature stores all the data in the memory to improve access time – which is now nearly 1000 times faster than that of a hard drive disk. You can also choose to duplicate the data into the disk to make it persistent.

In-Memory OLTP has the following characteristics:

  • Only hash and range indexes are available;
  • No buffer pool;
  • Stream-based storage for durability;
  • Full ACID support;
  • Core engine uses lock-free algorithms;
  • No lock manager, latches or spinlocks;
  • Use of T-SQL compiled to machine code, via C code generator (C compiler integrated in SQL Server 2014 engine);
  • Invoking procedure is just a DLL entry-point;
  • Aggressive optimization at compile-time.

Databases gains the same manageability, administration and development experience with SQL Server Integration:

  • Integrated queries and transactions;
  • Integrated High Availability and backup/restore.

For more information, please have a look at Stéphane Savorgnano’s posting  SQL Server 2014: In-memory OLTP project “Hekaton”.

ColumnStore

The query speed has been significantly improved when using column store indexes, with the use of a new batch mode.
This batch mode improves CPU utilization by:

  • Minimizing instructions per row;
  • Taking advantage of cache structures;
  • Highly efficient algorithms;
  • A better parallelism.

AlwaysOn

AlwaysOn has been enhanced with two minor changes. The first one is the number of secondaries that has been increased from four to eight, but the number of synchronized secondaries is still two. The second one is that it now allows FCI customers to configure CSV paths for system and user databases.

have a look at Stéphane Haby’s posting SQL Server 2014: AlwaysOn-Verfügbarkeitsgruppen – jetzt mit Microsoft Cloud!.

Permissions

There are four new permissions in SQL Server 2014:

  • CONNECT ANY DATABASE: is a server level permissions which allows to connect all current databases and any new databases that might be created in the future;
  • IMPERSONATE ANY LOGIN: is a server level permissions which allows a middle-tier process to impersonate the account of clients connecting to it, as it connects to databases;
  • SELECT ALL USERS SECURABLES: is a server level permission which allows to view data in all databases that the login has the permission to connect to;
  • ALTER ANY DATABASE EVENT SESSION: is a database level permission which allows a role to read all metadata.

Backup Encryption

You can encrypt your data while you are performing a backup, independently of the destination – whether internal or external. You have to specify the encryption algorithm and the encryptor when creating the backup.

The supported encryptions are:

  • AES 128;
  • AES 192;
  • AES 256;
  • Triple DES.

The supported encryptors are:

  • A Certificate;
  • An Asymmetric Key.

Microsoft Power BI

Power Query permits to search and access specific data across multiple sites, whether internal or external from your organization.
You can also transform and combine this data for relevant analysis, such as:
• Clean, transform, and shape;
• Merge and combine.

Power Pivot is an Excel feature for faster analytics and flexible modeling of large data sets, with in-memory technology.

PowerPivot-Example_20131126-140202_1.png

Power View allows to see the data in new ways with an interactive virtualization, such as a 3 D geospatial analysis with Power Map.

PowerPivot-Example_20131126-141859_1.png

Hybrid Cloud

Cloud backup, manual or automatic, has been simplified with point in time restore at instance level, and measures databases usage patterns to set backup frequency.
Moreover there is a fast disaster recovery with a low Recovery Time Objective, which is easy to deploy and manage.

Furthermore, a new wizard has been delivered to deploy database to SQL Server in Windows Azure Virtual Machine for easier use.

SQL Server 2014 CTP2: what is new from the CTP1?

In-Memory OLTP is now present into the SQL Server Engine: it does not require any additional actions to be installed. It allows some in-memory performance benefits: you do not need to rewrite your database application or to refresh your hardware. Moreover CTP2 enhancements include AlwaysOn support, increased T-SQL surface area, and ability to migrate existing objects to In-Memory OLTP.

In-Memory Updateable ColumnStore provides higher compression, richer query support and updateability of the existing ColumnStore for data warehousing workloads. It gives you even faster load speed, query performance, concurrency, and even lower price per terabyte.
SQL Server uses SSDs as an extension to the database buffer pool, allowing more in-memory processing and reducing disk I/O.
For the Resource Gouvernor, Resource pools now support configuration of minimum and maximum IOPS per volume.

Conclusion

There is a very strong will on the part of Microsoft to provide BI tools, in order to easily access, analyze and share data from any source.
Moreover, Microsoft has a real desire to strenghten Cloud utilization through Windows Azure and AlwaysOn.

But without a doubt, the flagship product of this new edition of SQL Server is In-Memory OLTP. It improves significantly performance and rapidity of your databases.
For more information have a look at the presentation of SQL Server 2014: In-Memory OLTP by Stéphane Haby & Stéphane Savorgnano.

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Nathan Courtine
Nathan Courtine

Microsoft Technology Leader and Senior Consultant