Qlik Replicate SQL Server Source: Rebuild Index Best Practices and Recommendations

[lyka]

This article covers a set of Best Practices and Recommendations when rebuilding the index of an SQL Server source.

Index:

• Background
• Recommended Process

Background

1. Always stop the Qlik Replicate tasks during the index rebuild. This is recommended due to the following:
   
   
   • Qlik Replicate’s reading from the active log may conflict with the rebuild index process and can lead to the replicate session locking the rebuild index process and then the rebuild index.
   • The process may cause a lock to other processes running in SQL Server, including major production processes that may be locked from updating the data on SQL Server database.
   • The rebuild index process itself can put a lock on system tables such as system_internals_partitions and thus locking the Replicate CDC process from reading changes, even on tables that their index is not currently rebuild. This can lead to various errors in replicate CDC replication.
   • CDC reading of changes if the task is running can be slower than reading the task is down, especially when reading backup logs. Every new rebuild index operation might cause the CDC query to stop and re-run as the CDC query is filtering the changes by partition id, and each rebuild index operation might change the partition id, and so the query has to be changed. Re-running the query again can take a long time on backup logs , as it means re-reading the backup log from start ,even if CDC was already in the middle of it.
     
     
2. Reading from the active log is usually much faster than reading from backup logs, although the different can vary in different environment, but usually reading from active is much faster.
   
   Another recommendation is to not run log backups at all during rebuild index, so Qlik Replicate will be able to read the changes from the active log. Since the volume of changes during rebuild index is huge, the difference in time between reading from active and backup can be significant.
   
3. Another option is to not stop log backup but do stop MS Replication during rebuild index so the Tlog will be backed up but not truncated, and resume MS replication after Qlik Replicate finishes consuming the changes from active log. This method also requires Qlik Replicate to be set the “Change processing mode (read changes from)”. To achieve this:
   1. Open the SQL server source endpoint setting
   2. Go to the Advanced tab
   3. Locate online log and make the required change
      

Recommended Process

1. Stop replicate task before rebuild index
2. Stop transaction log backup process before rebuild index Or stop MS-Replication process
3. Run rebuild index process
4. Once rebuild index done, let replicate consume all the changes
5. Once Qlik Replicate caught up with all the change and source latency is low, resume log backup (or MS-replication process)

If the recommend process is not possible:

1. Log backups can be taken, but we recommend frequent backups rather than singular large ones. It is recommended to set “Change processing mode (read changes from)” field in SQL server source endpoint.
2. If the task cannot be stopped during rebuild index, you may experience the issues mentioned in item (1).

[c_grigoriadis]

Great article! Really helpful.