best practice for Drop/Recreate tables source MSSQl
We are using Qlik Replicate with Microsoft SQL Server MS-CDC as the source. Replicated data is then consumed by Qlik Compose to build a DWH, including Type 2 historical tables.
In our source system, platform upgrades may change table structures not by using ALTER TABLE, but by dropping the existing table and recreating it with a new structure. The existing data is then copied into the newly created table.
During this process, new columns may be added. In some cases, these new columns are populated with default or calculated values during the upgrade itself, before CDC is enabled again for the recreated table.
After the upgrade is completed, the source system is started and new business transactions begin immediately.
For us, it is critical to:
not lose any changes that occur after the source system is started;
capture the values of newly added columns that were populated during the upgrade before CDC was enabled again;
preserve the existing Type 2 history in Qlik Compose.
We would like to understand the Qlik-supported best practice for restoring replication after such table recreation.
The obvious approach would be to remove/re-add the table in Replicate and perform a new Full Load. However, this is very expensive in our environment.
Some of the affected tables contain hundreds of millions of rows. A Full Load in Qlik Replicate takes significant time and generates considerable load on both the source and target. In addition, because the replicated data is consumed by Qlik Compose with Type 2 history, a new Full Load may require additional Full Load / Handle Duplicates processing in Compose, including hash recalculation. For large tables, this process is also very resource-intensive and time-consuming.
Could you please advise on the following:
What is the recommended and supported Qlik procedure for recovering MS-CDC replication after a source table has been dropped and recreated?
Is it possible to restore replication for such a table without performing a complete Full Load of the entire table?
What is the recommended way to capture values in newly added columns that were populated during the upgrade, before CDC was enabled again?
Is there any supported mechanism for performing a partial or limited reload/synchronization only for the recreated or affected data, and then continuing Change Processing from a specific LSN?
Is the following type of workflow supported:
record the LSN before the upgrade;
stop Replicate;
perform the source-system upgrade and table recreation;
enable CDC again;
refresh the table metadata in Replicate;
synchronize the data that was created or changed outside CDC;
continue Change Processing from the saved LSN,
without performing a complete Full Load of the large tables?
What is the recommended approach when the downstream Qlik Compose DWH contains Type 2 historical tables and we need to preserve the existing history without rebuilding large portions of the DWH?
We are specifically looking for a Qlik-supported best practice with minimal manual intervention in the target tables and minimal need for a complete Replicate + Compose reload cycle.
We are not looking for an unsupported manual manipulation of Replicate change tables, Compose metadata, or internal processing tables.
If a complete Full Load cannot be avoided in this scenario provide the recommended Qlik procedure to minimize downtime, source-system load, and reprocessing of large historical tables in Compose?
Hi, As I can see you appreciate, dropping and recreating tables is very much not ideal in a CDC pipeline, so you have some big challenges here, especially if new schemas have new fields with populated values for existing rows in the new version of the source tables.
The usual way to avoid a reload in Replicate would be to use advanced run modes, as you have mentioned, to process CDC only, but you would have some manual work to get the target table, and it's change table, into the same state as the modified source table for this to work. You then have the same issue in the Compose DW since it only knows changes that arrive as DML.
Depending on what schema changes are occurring, and how frequently, you could maybe look to have different versions of the table in the landing zone, there are mechanisms to combine landing tables vertically and horizontally into a DW entity.
Try a few things out in a Dev environment but to be honest, I highly recommend this is reviewed with Qlik Professional Services since they have seen this scenario before and can work out the nuances of your particular environment. Cheers!
The idea of keeping different versions of the table in the landing zone sounds particularly interesting
Could you please elaborate on what mechanism in Compose you had in mind for combining the different table versions vertically/horizontally into the same DW entity?
I understand that Advanced Run allows me to recover CDC changes after CDC has been re-enabled. However, there is still a gap between the pre-upgrade snapshot and the state of the recreated table at the moment CDC is enabled again.
What is the Qlik-supported mechanism for reconciling this gap without performing a complete reload of very large tables?
example before update ID | Name 1 | A 2 | B
after source update durin recreate tables ID | Name | Category 1 | A | X 2 | B | Y
and now we are enable CDC. we are missing Caregory column with data.
