Replication to Memory-Optimized Table Subscribers

Replication to Memory-Optimized Table Subscribers

 

Updated: October 28, 2015

Applies To: SQL Server 2016

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.

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.

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.

  • Tables replicated to memory-optimized tables on a subscriber are limited to the 8060 bytes per row limit of memory-optimized tables.

  • Tables replicated to memory-optimized tables on a subscriber are limited to the data types permitted in memory-optimized tables. For more information, see Supported Data Types for In-Memory OLTP.

  • Foreign key, unique constraint, triggers, schema modifications, ROWGUIDCOL, computed columns, data compression, alias data types, versioning, and locks are not supported in memory-optimized tables. See Transact-SQL Constructs Not Supported by In-Memory OLTP for information.

  • All columns in the key of an index must be specified as NOT NULL.

  • 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.

Replication Features and Tasks

Community Additions

ADD
Show:
© 2016 Microsoft