2

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

  • You article in not memory optimized on publisher and you want to replicate as a memory optimized table on subscriber? – SqlWorldWide May 4 '17 at 13:23
  • Yes, that is correct. – Reitse Eskens May 4 '17 at 13:24
2

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

  • Please see my blog post here: bit.ly/2gEfhXM It might answer some of your questions. – NedOtter May 7 '17 at 22:18
  • thank you for your link. It does answer some questions, but not the bug we ran into while testing some things (see the opening question). If you can reproduce the issue, please upvote the Connect issue. If you can't i'd realle like to read how you created the publication with a nvarchar(max) column. – Reitse Eskens May 8 '17 at 11:55
  • I confirmed this behavior, and upvoted your connect item. Definitely seems like a bug. – NedOtter May 9 '17 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.