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
Qlik Replicate
.png "Former Employee"){kind=icon}
