Skip to main content
Replicating tables that do not have a primary key

ON THIS PAGE

Replicating tables that do not have a primary key

Information note

This functionality is supported only for Microsoft SQL Server Enterprise edition.

By default, Qlik Replicate automatically sets up MS-REPLICATION for each of the source tables in a replication task. However, MS-REPLICATION requires each of the source tables to have a primary key, which may not always be the case. Therefore, if you need to replicate tables that do not have a primary key, the following options are available:

Use MS-CDC

To set up MS-CDC, you first need to enable MS-CDC for the database by running the following command:

use [DBname]

EXEC sys.sp_cdc_enable_db

Then you need to enable MS-CDC for each of the source tables by running the following command:

EXECUTE sys.sp_cdc_enable_table @source_schema = N'MySchema', @source_name = N'MyTable', @role_name = NULL;

Information note

Replicating tables that do not have a Primary Key or a Unique Index may adversely affect performance (since additional database resources are required to capture the changes). However, you can prevent performance issues related to the absence of Primary Keys or a Unique Index by manually adding indexes to the target tables.

For more information on setting up MS-CDC for specific tables, please refer to the Microsoft website.

Do not use MS-Replication or MS-CDC

If your database is not set up for MS-REPLICATION or MS-CDC, you can still capture tables that do not have a Primary Key, but bear in mind that in such a setup only INSERT/DELETE DML events will be captured. UPDATE events will be ignored.

It is also important to note that a DELETE statement executed on an UPDATED source record, will not be applied on the target.