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