topic Re: Azure SQL DB - Can't Exclude LOB Columns in Qlik Replicate

Azure SQL DB - Can't Exclude LOB Columns

jadams1 — Tue, 21 Nov 2023 20:06:07 GMT

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?

Re: Azure SQL DB - Can't Exclude LOB Columns

Dana_Baldwin — Tue, 21 Nov 2023 21:10:17 GMT

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

Re: Azure SQL DB - Can't Exclude LOB Columns

jadams1 — Tue, 21 Nov 2023 23:07:10 GMT

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?

Re: Azure SQL DB - Can't Exclude LOB Columns

Dana_Baldwin — Tue, 21 Nov 2023 23:12:57 GMT

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

Re: Azure SQL DB - Can't Exclude LOB Columns

jadams1 — Tue, 21 Nov 2023 23:13:48 GMT

Hi Dana,

It's jadams@greenpath.com.

Thank you,

Josh

Re: Azure SQL DB - Can't Exclude LOB Columns

jadams1 — Tue, 21 Nov 2023 23:23:22 GMT

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

Re: Azure SQL DB - Can't Exclude LOB Columns

shashi_holla — Tue, 21 Nov 2023 23:35:11 GMT

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

Re: Azure SQL DB - Can't Exclude LOB Columns

Dana_Baldwin — Tue, 21 Nov 2023 23:35:47 GMT

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

Re: Azure SQL DB - Can't Exclude LOB Columns

jadams1 — Tue, 21 Nov 2023 23:43:43 GMT

@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

Re: Azure SQL DB - Can't Exclude LOB Columns

Michael_Litz — Wed, 22 Nov 2023 00:00:50 GMT

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

Re: Azure SQL DB - Can't Exclude LOB Columns

shashi_holla — Wed, 22 Nov 2023 00:09:47 GMT

@jadams1

Thanks for joining the call on a quick notice.

As discussed, the issue is even before the data hits Replicate and on the Source DB itself because of the limitation of MS-CDC LOB data length on Azure DB. Don't think we can help much in this case and better to get help from Microsoft.

In the interim we can either have source lookup to get the delta or setup a passthru filter in Replicate to process the latest modified records, but you already know that it's not a clean approach and can miss updates or create duplicates in the target.

Re: Azure SQL DB - Can't Exclude LOB Columns

jadams1 — Wed, 22 Nov 2023 17:07:37 GMT

@Michael_Litz

Thank you for your suggestion, Michael. 😀

I had already tried that though. Qlik still creates the CDC definitions in Azure SQL DB for all columns. I am definitely not trying to be difficult here, so please understand no offense intended, but it seems a little wasteful from a resource perspective on the Replicate side to automatically include all columns in a CDC definition when columns are excluded in the Replicate task and CDC supports defining specific columns. I also tried turning off the create CDC definition inside of Replicate and manually creating the CDC definition. Then in Replicate only included the columns that I included in the definition for CDC for the table. In this scenario, Replicate errors on the task and complains that the definition of CDC is invalid.

Again, I really do appreciate your suggestions.

Thanks,
Josh

Re: Azure SQL DB - Can't Exclude LOB Columns

SushilKumar — Thu, 23 Nov 2023 04:31:45 GMT

Hello Team,

To add more on to that Any replication Tools follow the Same principal. The reason behind it . on the Source endpoint it has the connection to the database to get the metadata data of table (definition) . Hence it will fetch full information on the target Side it totally depends on the information which it collected from Source DB to find the Exact row and Colum for DML processing.

However manually you can fetch the definition as per your requirement.

Reagrds

Sushil Kumar