article Qlik Replicate and SQL Server Replication: Enable CDC in Official Support Articles

Qlik Replicate and SQL Server Replication: Enable CDC

David_Fergen — Mon, 22 May 2023 12:45:48 GMT

How to setup replication for on-premise Microsoft SQL source endpoint with enabling CDC.

Resolution

Setting up Microsoft SQL Server for Replication If you are using Microsoft SQL Server as the source in a Qlik Replicate task, you need to enable your Microsoft SQL Server database for MS-REPLICATION.

In the Microsoft SQL Server’s Management Studio, follow the instructions provided by the Configure Distribution wizard to set up replication or see the Microsoft SQL Server documentation. To open the wizard from Microsoft SQL Server:

In the Microsoft SQL Server Management Studio, right-click the Replication folder and select Configure Distribution. The Configure Distribution wizard opens.
Make the following selections: In the Distributor step, select will act as its own distributor; Microsoft SQL Server will create a distribution database and log. Replicating Tables that do not have a Primary Key

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-CD
#Do not use MS-Replication or MS-CDC

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;

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.

The information in this article is provided as-is and to be used at its own discretion. Depending on the tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Environment

Re: SQL Server Replication - Enable CDC

gulshanr — Wed, 17 Aug 2022 18:34:45 GMT

Can we enable both MS-Replication and MS-CDC for same database?

Re: SQL Server Replication - Enable CDC

Sonja_Bauernfeind — Thu, 06 Oct 2022 08:36:26 GMT

@gulshanr

Can you give us more information on what you are attempting to achieve so we can assist you with this question?

All the best,
Sonja

Re: SQL Server Replication - Enable CDC

gulshanr — Thu, 06 Oct 2022 11:44:44 GMT

We wanted to have CDC as well as MS replication at DB level, Butour tasks were failing. We applied some setting at task level and solved our issue.

Re: Qlik Replicate and SQL Server Replication: Enable CDC

MoeE — Wed, 06 Nov 2024 06:00:47 GMT

Hi,

Just confirming. This is only if you want to use CDC? I've been able to do full loads only in the past for this. Has this changed in newer versions where you now need MS-REPLICATION or MS-CDC enabled for full loads?

Regards,

Mohammed

Re: Qlik Replicate and SQL Server Replication: Enable CDC

Sonja_Bauernfeind — Wed, 13 Nov 2024 10:00:23 GMT

Hello @MoeE

I checked with one of our Qlik Replicate experts for you.

No, this is for Full Load only tasks, no CDC is required: no MS-Replication needed, nor MS-CDC needed.

All the best,
Sonja