topic Re: Connection methods for Microsoft SQL in Qlik Replicate

Connection methods for Microsoft SQL

ordenjaadm — Fri, 21 Oct 2022 07:37:42 GMT

Hello, good morning.

Reviewing the documentation it is not clear to us what are the available methods of connecting to a MS-SQL data source.

I mean if we want to do Delta (Incremental) extractions.

1 - Apart from the CDC option, is there any other alternative?

2 - Or within the CDC option there are different ways to make the connection/extraction?

Greetings and thanks

Re: Connection methods for Microsoft SQL

Heinvandenheuvel — Fri, 21 Oct 2022 14:20:24 GMT

Replicate is happy to read the Active Transaction Log and/or Archived Tlog and extract the changes for selected table from it to apply to a target and/or store in a changes table on some target database. It can find the Archived log location joining backupmediafamily and backupset system table. It can use MS SQL stored procedures to read the file, or read them directly if access is provided. This is a powerful, source transparent, convenient way to find all change on an ongoing, quasi-non-polling, near real time CDC method. It's the INTENDED, and best way to use Replicate. The 'connection' is just a valid logon to the DB using sufficiently privileged username (see pre-requisites).

MS SQL can also serve as Generic ODBC source the the base tables are queried for rows with a 'key' (sequence number, date&time stamp) higher than the last change read. See chapter "8.19 Using ODBC to connect to a source". That's an alternative, non-transparent, discouraged 'connection'.

Hein.

Re: Connection methods for Microsoft SQL

SwathiPulagam — Mon, 24 Oct 2022 18:22:47 GMT

Hi @ordenjaadm ,

In addition to @Heinvandenheuvel please refer to the below article which explains what Replicate supports for SQL server source:

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-MS-REPLICATION-vs-MS-CDC/ta-p/1939350

Thanks,

Swathi