In-Memory OLTP: High Availability for Databases with Memory-Optimized Tables

November 5, 2013

Starting with SQL Server 2012, the new offering of AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances (FCI) suite of features have enabled Tier-1 customers with mission critical applications to achieve their availability goals with SQL Server with an easy to deploy and manage solution. SQL Server 2014 builds on this success and offers enhanced AlwaysOn Availability Groups with up to 8 replicas, ability to access secondary replica for offloading reporting workload in disconnected scenario and hybrid scenario with Windows Azure.

In-memory OLTP is also targeting mission critical applications to deliver up to 30x better performance, and is integrated well with High Availability offerings SQL Server provides. The sections below review each of the High Availability offerings in the context of the in-memory OLTP solution. SQL Server 2014 offers four high-availability choices to customers as follows:

AlwaysOn Failover Cluster Instances (FCI): An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable.

In-Memory OLTP is fully integrated with FCI. A database with memory-optimized tables can be configured and managed similar to how you would manage a database with no memory-optimized objects. However, memory-optimized tables in the database will likely add to the recovery time because these tables need to be loaded into memory before the database can be accessed. In-memory OLTP engine loads data/delta file in parallel to minimize the time taken. The time to load the memory_optimized tables depends upon the size of memory-optimized tables, the number of data/delta files, IOPS available to read data/delta files and number of CPUs/Cores available. It is a best practice to configure SQL Server instances with same amount of memory as, unlike for disk-based tables, insufficient memory will cause database recovery to fail on the new instance. Please refer to How to manage your Memory for In-Memory OLTP for managing memory for database with memory-optimized tables

AlwaysON Availability Groups: SQL Server 2014 allows up to 8 secondary replicas including 2 sync secondary replicas with options to auto-failover. You can offload reporting workloads and database/log backups to one or more secondary replicas. This allows all resources on the primary replica to be solely used by the production workload leading to increased throughput. Each replica has its own copy of the databases in the availability group

In-memory OLTP is fully integrated with AlwaysOn Availability. You can setup a database with memory-optimized tables in an availability group following exactly the same step that you would for a database without memory-optimized tables. Key points

Impact on failover time: The memory-optimized tables are maintained in-memory and kept up-to date by the redo thread as it processes transaction log records. This ensures that the failover to secondary replica will not incur additional overhead of loading memory-optimized tables’ in-memory. Hence, unlike with FCI, there is no impact to failover time.
Readable Secondary: Querying memory-optimized tables both with natively compiled stored procedures and with SQL InterOP is supported on secondary replica. Unlike disk-based tables, the access to memory-optimized tables is done using optimistic concurrency therefore there is no need to map of isolation levels to snapshot isolation level done when accessing these tables. For this reason, reporting workload accessing memory-optimized tables are run without any isolation level mapping. The restrictions on isolation levels and hints are the same both on primary and secondary replica. Please refer to Guidelines for Transaction Isolation Levels with Memory-Optimized Tables for details. This ensures any query or reporting workload that can be run on secondary replica will run without requiring any changes on the primary replica and vice-versa. However, independent of the isolation level specified for reporting workload, SQL Server provides only read-committed isolation level consistency on secondary replicas for memory-optimized tables. Please refer to Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups) for details. In CTP2, you need to enable TF-9989 to query in-memory OLTP tables on the secondary replica.
Non-durable tables: AlwaysOn Availability groups use transaction log transport to populate tables on the secondary replica. Since the changes to non-durable tables are not logged, these tables are not populated on the secondary replica. However, their schema will exist on replicas as part of the objects contained within the database.
Performance: There are two aspects of performance. First, with the increased transactional throughput with in-memory OLTP, can the secondary replica keep up? The short answer is that it will depend on the workload. A workload with high transactional throughput can put pressure on the log transport as well as on redo thread. With in-memory OLTP, to apply changes to memory-optimized tables, the redo thread will not incur IO related latency like it does for disk-based tables because the data is already in-memory. Second is the performance of reporting workload on the secondary replica. Its performance should be comparable to the performance on the primary replica. One limitation is that there is no auto-stats update on memory-optimized tables. You will need to manually update the statistics on the primary replica to generate the up to date statistics which can then be available on secondary replica after redo thread processes the log records associated with the statistics. Please refer to Statistics for Memory-Optimized Tables for details.

Log Shipping: SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.

In-memory OLTP: Log shipping is fully supported on database with memory-optimized tables. A database with memory-optimized tables can be configured and managed similar to how you would manage a regular database

Transactional Replication: It is a popular solution for offloading read workload one or more subscribers especially when only a subset of data, not the whole database, is needed on the subscriber. It is also used to a lesser extent for High Availability.

In-memory OLTP: Replication is supported on databases with memory-optimized tables but the limitation is that you cannot use a memory-optimized table as an article in a publication or as part of a subscription. We will look into addressing this in a future release. In spite of this restriction, you can use memory-optimized tables in read-scale scenarios for read-mostly workloads as described below

Scenario: Let us say you have couple of disk-based tables t_disk1 and t_disk2 that are accessed heavily by read workloads. For your workload, you want to replicate these two tables on the subscriber however you want read-workload to run memory-optimized tables. You can achieve this by creating corresponding memory_optimized t_memopt1 and t_memopt2 and populating them using DML triggers on disk1 and t_disk2 tables. Any insert/delete or the update of the row, the triggered action will update the t_memopt1 and t_memopt2 accordingly. Essentially, you have two copies of the same table, one is disk-based and other is memory-optimized. Now you can direct your read-workload to the memory-optimized tables.