SQL Server 2016 SP1 Replication – Creation of Publication Stops with nvarchar(max) Not Supported with Memory Optimized Tables

replicationsql-server-2016

We're trying the following setup:

SQL Server replication on SQL Server 2016 SP1.
We're using Transactional replication on this server to push transactions to the subscriber.
We can select the table we're trying to replicate. We check the checkbox telling the publication that it needs to be memory optimized. The table contains one column of the NVARCHAR(MAX) type, which is supported in SQL 2016.

So far so good.

But when we create the publication, the following error occurs:

The type 'nvarchar(max)' is not supported with memory optimized tables.
Changed database context to 'StackExchange'. (.Net SqlClient Data Provider).

Any thoughts on a solution? My fallback will be transaction log shipping.

Found some links that indicate the nvarchar(max) datatype should be replicationable:
https://technet.microsoft.com/en-us/library/ms151254%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/table-and-row-size-in-memory-optimized-tables

Found another resource telling me that the @schema_option should be set to 0x40000000000. Still the same error…

use [StackExchange]
exec sp_addarticle @publication = N'T04'
    , @article = N'Badges'
    , @source_owner = N'3DPRINTING'
    , @source_object = N'Badges'
    , @type = N'logbased'
    , @description = N''
    , @creation_script = null
    , @pre_creation_cmd = N'drop'
    , @schema_option = 0x40000000000
    , @identityrangemanagementoption = N'manual'
    , @destination_table = N'Badges'
    , @destination_owner = N'3DPRINTING'
    , @vertical_partition = N'false'
    , @ins_cmd = N'CALL sp_MSins_3DPRINTINGBadges'
    , @del_cmd = N'CALL sp_MSdel_3DPRINTINGBadges'
    , @upd_cmd = N'SCALL sp_MSupd_3DPRINTINGBadges'
GO

I've created a Connect item with Microsoft for this issue.
https://connect.microsoft.com/SQLServer/feedback/details/3133643/replication-to-memory-optimized-table-fails

Best Answer

Fixed it. The @schema_option shouldn't be 0x40000000000 but 0x80000000000. This means the replication converts clustered index to nonclustered index for memory-optimized articles. It's nowhere close to error message.

So, if you need to create a replication from a disk table to a memory optimized table, do not let the wizard do the work but create the script en alter the @schema_option. At least this lets you create the publication and the subscriber can connect to the publication.

Bugger, not fixed. Yes, the creation of the entire replication procedure works great. But then, the new memory optimized tables do not get created. The only thing this stupid feature does is drop the table (whether they exist or not). And then starts complaining the target table does not exist.

Now trying to hack the @schema_option to combine the 0x40000000000 and 0x80000000000. Somehow these values cross a threshold that makes them readable to SQL Server. One or the other works (and results in failures or weird behavior) together nothing...

Still not working in version 17.1 of Management Studio.

-- edit november 2017

The newer versions of Management Studio seem to support the in-memory to in-memory replication. And the error handling has improved. So question marked as solved. If anyone still encounters issues, please add to this question :)

Related Question