·
4 min read

How Memory-Optimized Database Technology is Integrated into SQL Server 2014

SQL Server 2014 takes an integrated approach to in-memory database technology. In-Memory OLTP, which optimizes performance of OLTP workloads, is fully integrated in the core SQL Server relational database management system.

Now, what do we mean by “fully integrated”? In this post we highlight some of the aspects that demonstrate:

Installation

No special steps are needed to install In-Memory OLTP. Simply install the SQL Server 2014 Database Engine Services, and In-Memory OLTP will be available, provided you install an edition of SQL Server that supports the feature. The In-Memory OLTP components are part of the database engine services, and run as part of the sqlserver.exe process.

Databases

The same SQL Server database can have both traditional disk-based and the new memory-optimized tables. This means the size of the database is not restricted by the available memory. To optimize the performance of a database application, only the frequently accessed “hot” part of the data needs to be memory-optimized; the remainder can stay on disk.

Transact-SQL

In-Memory OLTP adds new memory-optimized tables for optimized in-memory storage, and natively compiled stored procedures for speeding up business logic execution. You can use the familiar Transact-SQL language, which we extended to mark specific tables as memory-optimized and to make stored procedures as natively compiled. For examples, see our post on Getting Started with In-Memory OLTP.

Memory-optimized tables, indexes and natively compiled stored procedures are reflected in the database metadata, which means you can find them in familiar catalog views, such as sys.tables, sys.indexes, and sys.sql_modules. In addition, In-Memory OLTP objects inherit the permission model of SQL Server, so you manage permissions and security for the new objects in the same, familiar way, as you would any other object in the database.

Queries

Memory-optimized tables can be accessed using the familiar SELECT/INSERT/UPDATE/DELETE/MERGE Transact-SQL statements. You can use virtually the full T-SQL surface area to access memory-optimized tables, with only a few restrictions, for example no cross-database queries and no locking hints. There are no restrictions on the types of batches or modules to access the memory-optimized tables from. You can access them from ad-hoc batches, stored procedures, triggers, views, table-valued functions, etc.

In addition, you can access both memory-optimized tables and disk-based tables in the same query, without restrictions (aside from the transaction-related restrictions, mentioned below). You can, for example, define a view that contains a JOIN or a UNION of a memory-optimized and a disk-based table.

Note that the new natively compiled stored procedures, introduced by In-Memory OLTP do have restrictions on the T-SQL surface area you can use inside them. For example, you cannot access disk-based tables from natively compiled procs, and MERGE and UNION are not supported. Again, these restrictions do not apply when accessing memory-optimized tables from ad hoc batches or traditional stored procedures.

Transactions and Logging

Memory-optimized tables and natively compiled stored procedures are fully transactional, like the traditional tables and stored procedures in SQL Server. Transactions on memory-optimized tables provide the usual ACID guarantees: Atomicity, Consistency, Isolation, and Durability. The first three guarantees (ACI) are provided by the In-Memory OLTP engine. Durability is guaranteed through the transaction log. All operations on memory-optimized tables are logged in the database transaction log. A SQL Server database has a single transaction log that contains log records for both the disk-based and the memory-optimized tables in the database.

Transactions in SQL Server can access both memory-optimized and disk-based tables. We do have a few limitations, which primarily originate from the fact that memory-optimized tables use a new transaction model for memory-optimized tables to avoid locking and blocking, and allow very good scalability. We will describe implications new transaction model in later posts. The main limitations in SQL 2014 (which we plan to address in a later release) are that cross-database transactions are not supported, and there are certain limitations on transaction isolation levels. We will discuss these limitations in a later post. For more details, see also Books Online.

Storage and Availability

In-Memory OLTP introduces a new type of filegroup (MEMORY_OPTIMIZED_DATA) for persistence of the data in memory-optimized tables. This filegroup is used during database startup to recover the data in the memory-optimized tables.

BACKUP and RESTORE are supported. The MEMORY_OPTIMIZED_DATA filegroup is included in database backups, and the log records for memory-optimized tables are part of the transaction log included in full and log backups.

Familiar high availability features are supported. SQL Server 2014 CTP2 will include support for In-Memory OLTP with AlwaysOn High Availability and Disaster Recovery technologies. Failover clustering will be supported for databases that have memory-optimized tables: the data in memory-optimized tables is recovered after failover to a secondary server. Databases with memory-optimized tables can be part of Availability Groups, using both synchronous and asynchronous replication, for local high availability as well as remote disaster recovery solutions. The data in memory-optimized tables is kept in memory on secondary instances, to enable seamless failover to a secondary replica in case of failure of the primary.

Familiar Tools and Client Connectivity

Memory-optimized tables and natively compiled stored procedures can be accessed and managed using the same tools as you would for traditional tables and procedures. For example, you can use Management Studio to manage the database and issue ad hoc queries, and use Integration Services to load data.

DMVs and performance counters are extended to work with the new In-Memory OLTP engine, to allow monitoring and troubleshooting similar to how you would monitor and troubleshoot the traditional SQL Server engine.

SQL Server client tools, such as SQL Native client and ADO.NET SqlClient, use the TDS protocol to communicate with SQL Server. We did not make any changes to TDS for supporting In-Memory OLTP. This means that you can use existing client tools to access memory-optimized tables, as well as natively compiled stored procedures.

We hope you find this information helpful, and we looking forward to bring you more on SQL Server 2014 in the weeks to come. In the meantime, download SQL Server CTP1and get started today! You can see more blogs in the series introduction and index here!