Hybrid Cloud and Hekaton Features in SQL Server 2014

Introduction

Microsoft SQL Server 2014 is considered to be the first version that supports Hybrid Cloud by adding a lot of exciting new features.

In this article, I will cover some of the top new features in these main points including Hekaton and Hybrid Cloud enhancements:

Hekaton

Hekaton is the code name of the new feature of In-Memory OLTP. It is a new database engine, fully integrated with SQL server and designed to enhance memory resident data and OLTP workloads. In simple words, with Hekaton we can store the entire table in memory.

Let’s list some of the benefits of this new feature:

• Memory-Optimized-Tables can be accessed using T-SQL like Disk-Based-Tables.
• Both of Memory-Optimized-Tables and Disk-Based-Tables can reference in the same query, and also we can update both types of tables by one transaction.
• Stored procedures that only reference Memory-Optimized-Tables can natively compile into machine code which results in improving performance.
• This new engine designed for a high level of session concurrency for OLTP transactions.

There are still some limitations for Memory-Optimized-Tables in SQL server 2014 which are:

• ALTER TABLE statement, SP_RENAME stored procedure, ALTER BUCKET_COUNT statement, and add\remove index outside statement of CREATE TABLE, all of these not supported by In-Memory table
• Some constraints not supported like (CHECK, FOREIGN KEY, UNIQUE)
• RANGE INDEXES and TRIGGERS not supported by In-Memory table
• REPLICATION, MIRRORING, and LINKED SERVERS are incompatible with Memory-Optimized-Tables.

To know more information, you can check SQL Server Support for In-Memory OLTP.

Memory-Optimized-Tables are appropriate for the following scenarios:

• A table has a high insertion rate of data from multiple concurrent sources
• A table cannot meet scale-up requirements for high performance of reading operations especially with periodic batch inserts and updates
• Intensive logic processing inside a stored procedure
• A database solution cannot achieve low latency business transaction

Let’s now go through the steps to create a Memory-Optimized-Table

 

For more information check the source article from HERE