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