Does resuming the Qlik replicate incremental task captures all the pending changes from source
Hi Everyone,
There is a need to load the data into target at every 6-hour interval, 4 times a day. for which, have scheduled the task to stop and start with 6 hours interval.
I tried with this start-stop approach but mostly faced error while re-starting the task.
[SQL Server]Invalid parameter passed to OpenRowset(DBLog, ...)
Already have ignoreTxnCtxValidityCheckand set it toTrue.
Have gone through different article present in the Qlik Community for the above error:
and caused by "If there is no access to the backups and the log has been truncated, the error is thrown, and the task fails. "
Questions:
1. does resuming the Qlik replicate incremental task captures all the pending changes from source?
NOTE: The task restarts manually but restarting the task after this error, does not capture the changes - have analyzed the changes captured.
2. how does we ensure, the logs and backups are not truncated?
as Qlik Replicate prevents TLOG truncation by mimicking a transaction in the database, using Start transactions in the database advance option.
As long as such a transaction is open, changes that appear after the transaction started will not be truncated.
3. what does transaction means in the above statement?
The error message you shared means that the LSN the task needs to resume from where it left off from is not available in either the online or backup TLOG.
The first reason for this would be if the backup TLOG has been purged during the time the task was stopped. Please extend the time the backup TLOG is available on disk.
The second reason for this would be if the backup TLOG is not in native SQL Server format, or is encrypted. Please check these items.
For your questions:
1. Yes, resuming a CDC task will pick up from where it left off provided the TLOG is still available and readable as noted above.
2. At some point the log will be truncated and the data moved to the backup TLOG. We try to keep the changes needed in the online TLOG by using the transactions, but this does not prevent movement of data to the backup TLOG 100% of the time. The reason we try to keep changes in the online TLOG is so we are not switching back and forth between reading from the online and the backup. There are some use cases where reading only from the backup TLOG is more efficient, but latency will never be lower than your backup frequency in this configuration. If you need to examine your implementation options for this, engaging our Professional Services would be the right next step, please check with your Account Manager. Support can answer questions on how it works though so no problem there - but when it comes to advice based on an in-depth examination of your use case - Professional Services is needed.
3. A DML transaction such as an update statement that we start but do not commit for 5 minutes per the default setting on the endpoint.
The error message you shared means that the LSN the task needs to resume from where it left off from is not available in either the online or backup TLOG.
The first reason for this would be if the backup TLOG has been purged during the time the task was stopped. Please extend the time the backup TLOG is available on disk.
The second reason for this would be if the backup TLOG is not in native SQL Server format, or is encrypted. Please check these items.
For your questions:
1. Yes, resuming a CDC task will pick up from where it left off provided the TLOG is still available and readable as noted above.
2. At some point the log will be truncated and the data moved to the backup TLOG. We try to keep the changes needed in the online TLOG by using the transactions, but this does not prevent movement of data to the backup TLOG 100% of the time. The reason we try to keep changes in the online TLOG is so we are not switching back and forth between reading from the online and the backup. There are some use cases where reading only from the backup TLOG is more efficient, but latency will never be lower than your backup frequency in this configuration. If you need to examine your implementation options for this, engaging our Professional Services would be the right next step, please check with your Account Manager. Support can answer questions on how it works though so no problem there - but when it comes to advice based on an in-depth examination of your use case - Professional Services is needed.
3. A DML transaction such as an update statement that we start but do not commit for 5 minutes per the default setting on the endpoint.
