Replication to Memory-Optimized Table Subscribers

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Tables acting as snapshot and 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. This feature is available beginning with SQL Server 2016.

Two configurations are required

To configure a memory-optimized table as a subscriber

  1. Create a transactional publication. For more information, see Create a Publication.

  2. Add articles to the publication. For more information, see Define an Article.

    If configuring by using Transact-SQL set the @schema_option parameter of the sp_addarticle stored procedure to
    0x40000000000.

  3. In the article properties window set Enable Memory optimization to true.

  4. Start the Snapshot Agent job to generate the initial snapshot for this publication. For more information, see Create and Apply the Initial Snapshot.

  5. Now create a new subscription. In the New Subscription Wizard set Memory Optimized Subscription to true.

    Memory-optimized tables should now start receiving updates from the publisher.

Reconfigure an existing transaction replication

  1. Go to subscription properties in Management Studio and set Memory Optimized Subscription to true. The changes are not applied until the subscription is reinitialized.

    If configuring by using Transact-SQL set the new @memory_optimized parameter of the sp_addsubscription stored procedure to true.

  2. Go to the article properties for a publication in Management Studio and set Enable Memory optimization to true.

    If configuring by using Transact-SQL set the @schema_option parameter of the sp_addarticle stored procedure to
    0x40000000000.

  3. Memory optimized tables do not support clustered indexes. To have replication handle this by converting it to nonclustered index on the destination, set Convert clustered index to nonclustered for memory optimized article to true.

    If configuring by using Transact-SQL set the @schema_option parameter of the sp_addarticle stored procedure to 0x0000080000000000.

  4. Regenerate the snapshot.

  5. Reinitialize the Subscription.

Remarks and Restrictions

Only one-way transactional replication is supported. Peer-to-peer transactional replication is not supported.

Memory-optimized tables cannot be published.

Replication tables on the distributor cannot be configured as memory-optimized tables.

Merge replication cannot include memory-optimized tables.

At the subscriber, tables involved in transactional replication can be configured as memory optimized tables, but the subscriber tables must meet the requirements of memory-optimized tables. This requires the following restrictions.

Modifying a schema file

  • If using the memory-optimized table option DURABILITY = SCHEMA_AND_DATA the table must have a nonclustered primary key index.

  • ANSI_PADDING must be ON.

See Also

Replication Features and Tasks