topic Re: Performing CDC on a SQL Server Database in Qlik Replicate

Performing CDC on a SQL Server Database

nabeelaslam1994 — Mon, 12 Jun 2023 12:59:46 GMT

Hello,

I am trying to see if Microsoft SQL Server 2014 Standard Edition(64-bit) supports CDC replication? Can someone provide some details on that possibly a link?

Thanks,

Nabeel

Re: Performing CDC on a SQL Server Database

john_wang — Mon, 12 Jun 2023 13:11:22 GMT

Hello @nabeelaslam1994 ,

Thanks for reaching out!

You are right , Standard Edition can be used as source database, see Supported editions.

Hope this helps.

Regards,

John.

Re: Performing CDC on a SQL Server Database

nabeelaslam1994 — Mon, 12 Jun 2023 13:16:11 GMT

Can it be possible using this? Using Microsoft SQL Server (MS-CDC) as a source instead of Using Microsoft SQL Server as a source?

Nabeel

Re: Performing CDC on a SQL Server Database

Arun_Arasu — Mon, 12 Jun 2023 13:17:10 GMT

Hello @nabeelaslam1994

Thanks for reaching out to Qlik Community.

Yes , Qlik Replicate Supports Microsoft SQL server Standard edition as a source and Target as well

Below is for your reference

https://help.qlik.com/en-US/replicate/May2023/Content/Replicate/Main/SQL%20Server/Supported_editions.htm

https://help.qlik.com/en-US/replicate/May2023/Content/Global_Common/Content/SharedReplicateHDD/SQLServer-Source/Supported_editions.htm

Below is the support matrix for May 2023 Qlik Replicate , please refer them for the list of supported endpoints and their versions

https://help.qlik.com/en-US/replicate/May2023/pdf/Replicate-Support-Matrix.pdf

Regards

Arun

Re: Performing CDC on a SQL Server Database

nabeelaslam1994 — Mon, 12 Jun 2023 13:22:34 GMT

@Arun_Arasu ^^

Re: Performing CDC on a SQL Server Database

john_wang — Mon, 12 Jun 2023 13:30:55 GMT

Hi @nabeelaslam1994 ,

Thanks for update.

For SQL Server 2014 Standard Edition you may use 2 different endpoints:

1- "Microsoft SQL Server"

In this endpoint Replicate will read changes records from SQL Server TLOG

2- "Microsoft SQL Server (MS-CDC)"

In this endpoint Replicate will utilize SQL Server itself MS-CDC functionalities, read changes from Change table and apply to target DB.

Please take care that in different endpoint there are different behaviors, for example in MS-CDC endpoint , Capture of DDLs is not supported however it's supported in "Microsoft SQL Server" endpoint.

Regards,

John.

Re: Performing CDC on a SQL Server Database

nabeelaslam1994 — Mon, 12 Jun 2023 13:40:42 GMT

When selecting one of the other, Is there a recommended endpoint to use when configuring for source?

Nabeel

Re: Performing CDC on a SQL Server Database

nabeelaslam1994 — Mon, 12 Jun 2023 13:46:38 GMT

@john_wang @Arun_Arasu I checked online and found this. Is this true ??

"No, the sp_cdc_enable_db stored procedure is not supported in the Standard Edition of SQL Server 2014. The Change Data Capture (CDC) feature is only available in the Enterprise and Developer editions of SQL Server 2014. If you need to use CDC in SQL Server 2014 Standard Edition, you may need to consider other third-party tools or custom solutions."

Re: Performing CDC on a SQL Server Database

john_wang — Mon, 12 Jun 2023 15:58:56 GMT

Hi @nabeelaslam1994 ,

I apologize for my previous inaccurate comment.

You are right, I had confirmed that the sp_cdc_enable_db stored procedure is not supported in the Standard Edition of SQL Server 2014. The messages like:

EXECUTE sys.sp_cdc_enable_db;

Msg 22988, Level 16, State 1, Procedure sp_cdc_enable_db, Line 14
This instance of SQL Server is the Standard Edition (64-bit). Change data capture is only available in the Enterprise, Developer, and Enterprise Evaluation editions.

select @@version;

Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
Sep 7 2018 01:37:51
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: ) (Hypervisor)

Hence MS-CDC is not an option for you.

Regards,

John.