Delen via

Replication to Memory-Optimized Table Subscribers

  • Artikel

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.

Configuring a memory-optimized table as a subscriber

To configure a memory-optimized table as a subscriber, perform the following steps.

Create and Enable Publication

  1. Create a publication.

  2. Add articles to the publication. For the @upd_cmd parameter, use the SCALL or SQL convention.

    EXEC sp_addarticle  
    @publication = N'Publication1',  
    @article = N'Mem_Table',  
    @source_owner = N'dbo',  
    @source_object = N'Mem_Table',  
    @type = N'logbased',  
    @description = null,  
    @creation_script = null,  
    @pre_creation_cmd = N'none',  
    @schema_option = 0x00000000080050DF,  
    @identityrangemanagementoption = N'manual',  
    @destination_table = N'Mem_Table',  
    @destination_owner = N'dbo',  
    @vertical_partition = N'false',  
    @ins_cmd = N'CALL sp_MSins_Mem_Table',  
    @del_cmd = N'CALL sp_MSdel_Mem_Table',  
    @upd_cmd = N'SCALL sp_MSupd_Mem_Table';  
GO

Generate a Snapshot and Adjust the Schema

  1. Create a snapshot job and generate a snapshot.

    EXEC sp_addpublication_snapshot @publication = N'Publication1', @frequency_type = 1;  
EXEC sp_startpublication_snapshot @publication = N'Publication1';

  2. Navigate to the snapshot folder. The default location is "C:\Program Files\Microsoft SQL Server\MSSQL12.<INSTANCE>\MSSQL\repldata\unc\XXX\YYYYMMDDHHMMSS\".

  3. Locate the .SCH file for your table and open it in Management Studio. Change the table schema and update the stored procedure as described below.

    Evaluate the indexes defined in the IDX file. Modify CREATE TABLE to specify the required indexes, constraints, primary key, and memory-optimized syntax. For memory-optimized tables, index columns should be NOT NULL and index columns of character types must be Unicode and use BIN2 collation. See example below:

    SET ANSI_PADDING ON;  
GO  

SET ANSI_NULLS ON;  
GO  

SET QUOTED_IDENTIFIER ON;  
GO  

CREATE TABLE [dbo].[Mem_Table]([c1] [int] NOT NULL,  
    [c2] [float] NOT NULL,  
    [c3] [decimal](10, 2) NOT NULL,  
    [c4] [nvarchar](5) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,  
    INDEX [hash_index_sample_memoryoptimizedtable_c2] HASH (c2) WITH (BUCKET_COUNT = 1024),  
    INDEX [index_sample_memoryoptimizedtable_c3] NONCLUSTERED ([c3]),  
    INDEX [nvarchar_index_sample_memoryoptimizedtable_c4] ([c4]),  
    CONSTRAINT [PK_sample_memoryoptimizedtable] PRIMARY KEY NONCLUSTERED ([c1])) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);  
GO

  4. When using SCALL convention for the @upd_cmd parameter, go to the schema (.SCH) file and change the table update statement in create procedure [sp_MSupd_<SCHEMA><TABLE_NAME>] to remove primary key columns.

    To support primary key updates, use a custom update stored procedure to replace the primary key update statement, as follows:

    1. Select missing column values (SCALL only provides the column involved into the update operation).

    2. Delete the existing record.

    3. Insert a new record with new values provided including the new primary key.

    The original update procedure looks like this:

    create procedure [sp_MSupd_Mem_Table]  
               @c1 int = NULL,  
               @c2 float = NULL,  
               @c3 decimal(10,2) = NULL,  
               @c4 nvarchar(5) = NULL,  
               @pkc1 int = NULL,  
               @bitmap binary(1)  
as  
begin    
if (substring(@bitmap,1,1) & 1 = 1)  
begin   
update [dbo].[Mem_Table] set  
               [c1] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [c1] end,  
               [c2] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [c2] end,  
               [c3] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [c3] end,  
               [c4] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [c4] end  
where [c1] = @pkc1  
if @@rowcount = 0  
    if @@microsoftversion>0x07320000  
        exec sp_MSreplraiserror 20598  
end    
else  
begin   
update [dbo].[Mem_Table] set  
               [c2] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [c2] end,  
               [c3] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [c3] end,  
               [c4] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [c4] end  
where [c1] = @pkc1  
if @@rowcount = 0  
    if @@microsoftversion>0x07320000  
        exec sp_MSreplraiserror 20598  
end   
end   
go

    If the primary key should never be updated on a publisher. Comment out the update to such columns in the update procedure as follows:

    create procedure [sp_MSupd_Mem_Table]  
               @c1 int = NULL,  
               @c2 float = NULL,  
               @c3 decimal(10,2) = NULL,  
               @c4 nvarchar(5) = NULL,  
               @pkc1 int = NULL,  
               @bitmap binary(1)  
as  
begin    
if (substring(@bitmap,1,1) & 1 = 1)  
begin   
update [dbo].[Mem_Table] set  
--             [c1] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [c1] end,  
               [c2] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [c2] end,  
               [c3] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [c3] end,  
               [c4] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [c4] end  
where [c1] = @pkc1  
if @@rowcount = 0  
    if @@microsoftversion>0x07320000  
        exec sp_MSreplraiserror 20598  
end    
else  
begin   
update [dbo].[Mem_Table] set  
               [c2] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [c2] end,  
               [c3] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [c3] end,  
               [c4] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [c4] end  
where [c1] = @pkc1  
if @@rowcount = 0  
    if @@microsoftversion>0x07320000  
        exec sp_MSreplraiserror 20598  
end   
end   
go

    To allow the support of updates to the primary key, modify the update procedure to read as follows

    create procedure [sp_MSupd_Mem_Table]  
               @c1 int = NULL,  
               @c2 float = NULL,  
               @c3 decimal(10,2) = NULL,  
               @c4 nvarchar(5) = NULL,  
                @pkc1 int = NULL,  
               @bitmap binary(1)  
as  
begin    
if (substring(@bitmap,1,1) & 1 = 1)  
begin   
select  
               @c1 = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [c1] end,  
               @c2 = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [c2] end,  
               @c3 = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [c3] end,  
               @c4 = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [c4] end  
from [dbo].[Mem_Table] where [c1] = @pkc1  
if @@rowcount <> 0 begin  
        delete [dbo].[Mem_Table] where [c1] = @pkc1  
        if @@rowcount <> 0  
               insert into [dbo].[Mem_Table]([c1],  
                       [c2],  
                       [c3],  
                       [c4]) values (  
                       @c1,  
                       @c2,  
                       @c3,  
                       @c4  
               )   
end  
if @@rowcount = 0  
    if @@microsoftversion>0x07320000  
        exec sp_MSreplraiserror 20598  
end    
else  
begin   
update [dbo].[Mem_Table] set  
               [c2] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [c2] end,  
               [c3] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [c3] end,  
               [c4] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [c4] end  
where [c1] = @pkc1  
if @@rowcount = 0  
    if @@microsoftversion>0x07320000  
        exec sp_MSreplraiserror 20598  
end   
end   
go

  5. Create subscriber database using the elevate to snapshot isolation option and set the default collation to Latin1_General_CS_AS_KS_WS in case of using non Unicode character data types.

    CREATE DATABASE [Sub]   
CONTAINMENT = NONE   
ON PRIMARY ( NAME = [Sub], FILENAME = [C:\Program Files\Microsoft SQL Server\MSSQL12\MSSQL\DATA\Sub.mdf]),   
FILEGROUP [mem] CONTAINS MEMORY_OPTIMIZED_DATA ( NAME = [mem],   
FILENAME = [C:\Program Files\Microsoft SQL Server\MSSQL12\MSSQL\DATA\Sub])  
LOG ON ( NAME = [Sub_log], FILENAME = [C:\Program Files\Microsoft SQL Server\MSSQL12\MSSQL\DATA\Sub.ldf])  
COLLATE Latin1_General_CS_AS_KS_WS;  

ALTER DATABASE [Sub] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;  
GO

  6. Apply the schema to a subscriber's database and save the schema for future use.

  7. Load the publisher (source) data to the subscriber. Data should not change at the publisher until you add a subscription. You can use BCP as shown below:

    bcp Pub.dbo.Mem_Table out Mem_Table.bcp -S. -T -C1252 -n  
bcp Sub.dbo.Mem_Table in Mem_Table.bcp -S. -T -C1252 -n

  8. Reconfigure the article to disable schema changes on the subscriber:

    EXEC sp_changearticle  
    @publication = N'Publication1',  
    @article = N'Mem_Table',  
    @property = N'schema_option',  
    @value = 0,  
    @force_invalidate_snapshot = 1,  
    @force_reinit_subscription = 1;  
GO

Add no sync Subscription

Add a nosync subscription.

EXEC sp_addsubscription  
    @publication = N' Publication1',  
    @subscriber = @@ServerName,  
    @destination_db = N'Sub',  
    @subscription_type = N'Push',  
    @sync_type = N'replication support only',  
    @article = N'all',  
    @update_mode = N'read only',  
    @subscriber_type = 0;  
GO

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

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.

  • To create a memory-optimized table on a transactional replication subscriber, the snapshot schema files used to create the memory-optimized tables must be manually modified. For more information, see Modifying a schema file.

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

  • There are restrictions on updating the primary key of tables being replicated to a memory-optimized table on a subscriber. For more information, see Replicating changes to a primary key.

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

Modifying a schema file

  • Clustered indexes are not supported. Change any clustered indexes to nonclustered indexes.

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

Replicating changes to a primary key

The primary key of a memory-optimized table cannot be updated. To replicate a primary key update on a subscriber, modify the update stored procedure to deliver the update as a delete and insert pair.

See Also

SQL Server Replication