High Availability Support for In-Memory OLTP databases
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Databases containing memory-optimized tables, with or without native compiled stored procedures, are fully supported with AlwaysOn Availability Groups. There is no difference in the configuration and support for databases which contain In-Memory OLTP objects as compared to those without.
When an in-memory OLTP database is deployed in AlwaysOn Availability Group configuration with readable secondary, the data changes on the primary replica are visible on the secondary replica when REDO is applied. In SQL Server 2016 Community Technology Preview 2 (CTP2), the read timestamp on the secondary replica is in close synchronization with the read timestamp on the primary replica. This close synchronization behaviour is different from SQL Server 2014 In-Memory OLTP.
Configuring databases with In-Memory OLTP components provides the following:
A fully integrated experience
You can configure your databases containing memory-optimized tables using the same wizard with the same level of support for both synchronous and asynchronous secondary replicas. Additionally, health monitoring is provided using the familiar AlwaysOn dashboard in SQL Server Management Studio.
Comparable Failover time
Secondary replicas maintain the in-memory state of the durable memory-optimized tables. In the event of automatic or forced failover, the time to failover to the new primary is comparable to disk-bases tables as no recovery is needed. Memory-optimized tables created as SCHEMA_ONLY are supported in this configuration. However changes to these tables are not logged and therefore no data will exist in these tables on the secondary replica.
You can access and query memory-optimized tables on the secondary replica if it has been configured for read access.
To achieve high-availability in a shared-storage configuration, you can set up failover clustering on instances with one or more database with memory-optimized tables. You need to consider the following factors as part of setting up an FCI.
Recovery Time Objective
Failover time will likely to be higher as the memory-optimized tables must be loaded into memory before the database is made available.
Be aware that SCHEMA_ONLY tables will be empty with no rows after the failover. This is as designed and defined by the application. This is exactly the same behavior when you restart an In-Memory OLTP database with one or more SCHEMA_ONLY tables.
Tables acting as transactional replication subscribers, excluding Peer-to-peer transactional replication, can be configured as memory-optimized tables. Other replication configurations are not compatible with memory-optimized tables. For more information see Replication to Memory-Optimized Table Subscribers.