Error in the Qlik Replicate task using Azure SQL Managed Instance

[Naga_vivek]

Hi Team,

We replaced  MSSQL Server source endpoint with newly created "Azure SQL Managed Instance" source endpoint the task as the task is throwing errors in May 2023 if we use MSSQL Server source endpoint for Microsoft Azure MI connection, suggested by your team.

It is working fine but after monitoring
for few days it is throwing the below errors. could you suggest how we can resolve the issue.

Replicate Version : May 2023
Task is in Stream component recoverable error state and not running.

1.Encountered an unexpeceted error. Aborting...
mssql_execute_log_lsn_peeper(...) failed upon SQLExecute with LSN parameters '<LSN values>'
2.Stream component '<st_0_source_MI _Endpoint>' terminated
Stream component failed at subtask 0, component st_0_source_MI _Endpoint
Error executing source loop
3.LSN '<LSN number>' could not be found, neither in the transaction log nor in the backup log
4.Encountered an unexpeceted error.
Bad Envelope :
Lsn=<LSN number>,operation=<operationno>,TxnId=<TxnId>,Tablename=ROLLBACK,PageId=,slotId=0,timeStamp=2024-01-10T01:48:23.563,dataLen=0, LCX=<LCX number>,
>Invalid data context / LCX Code encountered for TXN operation.

Please suggest any process to resolve the below issues. what could be the reason for getting the issues?

Can we expect the same issues might occur if we use Azure SQL Managed Instance as an endpoint for the Replicate tasks?

Thanks & Regards

N L N Vivek

[Dana_Baldwin]

Hi @Naga_vivek 

This means that the LSN the task needs to continue without missing any changes has been moved from the online transaction log to the backup. Please note the following limitations from our User Guide:

• CDC is supported with the transaction log only, with no option to work with backup.
• Events may be lost if they are moved to backup or truncated.

Also:

While it is certainly possible to capture changes from a Microsoft Azure SQL Managed Instance, due to its limitations (see below), it is recommended to use the newer Microsoft Azure SQL (MS-CDC) endpoint instead.

Please see: Setting up a Microsoft Azure SQL Managed Instance for CDC | Qlik Replicate Help

Change the release version at the top left of the screen for information on the version you are using.

Hope this helps,

Dana

[Naga_vivek]

Hi @Dana_Baldwin ,

Thanks for your reply.

We also observed some database unavailability issue in between is that the reason we could have this above LSN issues or will there be any reason why we will get this.
Error :
1/10/2024 7:35:22 AM ERROR_GENERAL "Stream component 'st_0_source_MI _Endpoint' terminated
Cannot initialize subtask
Failed while preparing stream component 'st_0_source_MI _Endpoint'.
Failed to connect
RetCode: SQL_ERROR SqlState: HY000 NativeError: 40613 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Database '<source database>' on server '<source database server>' is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of '<tracing id>'."

If we get the same LSN issues again is there any way we can resolve them without modifying the Endpoint type. Please let us know any if we can resolve them using Azure SQL Managed Instance type

Thanks & Regards

N L N Vivek

[Dana_Baldwin]

Hi @Naga_vivek 

It is possible that the unavailability of the database made the issue worse, as the longer the database is running without the task being able to read and process the changes, the more likely it is that changes will be moved to the backup TLOG before they are read.

I am not aware of a way to decrease the backup frequency of the TLOG but you might check with Azure/Microsoft Support to inquire about it. Keeping changes in the online log until they are read is the only way to prevent this issue, but our task cannot directly impact that.

I apologize but I do not have more details as to how the Microsoft Azure SQL (MS-CDC) endpoint can help this situation. If more information is needed, please open a support case or contact your Customer Success representative if you have one.

Thanks,

Dana