Azure SQL DB - Can't Exclude LOB Columns

[jadams1]

I am running into an issue replicating data from Azure SQL DB via MS-CDC. I have several tables that have columns with  nvarchar(max) as a data type. Some columns have data inside that exceed the 65k default limit set for MS-CDC and we can't alter the maximum limit because the source is Azure SQL DB. I have tried manually defining MS-CDC definitions to exclude nvarchar(max) columns and excluding the columns in the Replicate tasks, but I get the following error when running the Replicate task "The metadata for source table 'counseling.CreditReport' is different than the corresponding MS-CDC Change Table. The table will be suspended. (sqlserver_mscdc.c:776)". I have also tried telling Replicate not to include LOB columns but Replicate automatically includes the columns in the MS-CDC definition and MS-CDC errors out when a column with data larger than 65kb is processed.

Are there any workarounds for this issue?

[Dana_Baldwin]

Hi @jadams1 

Please open a support case for this issue as we may need to involve our internal support team to resolve it.

Thanks,

Dana

[jadams1]

Thank you for the response @Dana_Baldwin. I'm having issues opening a support case, tried the chatbot and was told that there most likely is a problem on the backend. Would you be so kind as to help me open a support case?

[Dana_Baldwin]

Hi @jadams1 

Did you chat with a live agent? If your work email address is not associated with a customer account in our system, we won't be able to open a case without help from a different support team. What is your work email address?

Thanks,

Dana

[jadams1]

Hi Dana,

It's jadams@greenpath.com.

Thank you,

Josh

[jadams1]

I forgot to mention that the chatbot said there were no live agents available.

[shashi_holla]

@jadams1 

There is no actual limit of 65KB on Azure SQL DB via MS-CDC. Default value is 65KB which can be reconfigured to allow unlimited and your DBA team can help with the same.

Also, on the exclusion of LOB column via Replicate, following setting should help:

Task Settings -> Target Metadata -> uncheck the option "Replicate LOB columns"

Save the changes and load the table, LOB columns shouldn't be replicated to the target.

Let me know if you have any questions.

Thank you,

[Dana_Baldwin]

Hi @jadams1 

I apologize that no agents were able to help you at the time. I've emailed the team that supports accounts in our portal and they should follow up with you.

Thanks,

Dana

[jadams1]

@shashi_holla 

Server-side settings such as "max text repl size" are not configurable in Azure SQL DB via sp_configure. We reached out to Microsoft support to see if they might change the setting for us on the instance that our databases are on, and they said they would not change the setting. My guess is that since Azure SQL DB is a shared service, they are concerned it would impact other customers on the same instance.

I tried your second option multiple times and Replicate will still include the LOB columns in the MS-CDC replication definition on the table.

Thank you for the jumping into the conversation and offering suggestions.

Thanks,

Josh

[Michael_Litz]

Hi @jadams1 

You can try this brute force option.

On each table in the transformations screen remove the lob columns from the the target side list and see if that works.

Thanks,
Michael