What exactly are Publications in SQL Server source used for?

[MoeE]

Hi,

I can't find any in depth information on how exactly Publications on a SQL Server source is used or what they're used for. I was able to perform a full load and inserts without any Publications created on the source. After a publication was created, UPDATE and DELETE was possible.

Is it correct to assume it is mainly for UPDATE and DELETE for CDC? 

I've read this article https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-MS-REPLICATION-vs-MS-CDC/ta-p...

which says "SQL Server publications and articles, to write additional data about DML and DDL operations into SQL Server TLOG. This information will be later read and then parsed by Qlik Replicate to be distributed to the target endpoint of your choice"

and  

"Qlik CDC based on the MS-REPLICATION method leverages the transactional replication mechanism available in the SQL Server."

So my current understanding is that  Publications are created for CDC and mainly UPDATE and DELETE statements?

Please correct me if I'm wrong and apologies for my messy explanations. Any guidance on this is appreciated. Thank you.

Kind Regards,

Mohammed

[SachinB]

Hello @MoeE ,

Thanks for reaching  support.

By default, Qlik Replicate tries to use Microsoft SQL Server native replication feature to support capture changes from the SQL Server source.

When we start the Qlik Replication task with SQL Server as a source, Qlik Replicate will verify that the MS Replication feature is enabled or not, and once all the pre-requisites are met Qlik replicate will try to create the publication with the name AR_PUBLICATION_XXXX where XXXX represents the dbid of the Replicated database as shown in the below picture. Which help for the CDC changes

Note: Each time when we stop and resume the task, Qlik Replicate will verify whether the publication exists with the name AR_PUBLICATION_XXXX and if the publication does not exist then Qlik Replicate will automatically create the publication with the name AR_PUBLICATION_<dbid>.

https://community.qlik.com/t5/Official-Support-Articles/How-does-Qlik-Replicate-define-the-publicati...

Let me know in case if you need additional information for the same.

Regards,

Sachin B

[MoeE]

Hi Sachin,

Thanks for the answer I really appreciate it. I have a follow u question, is it possible to manually create a publication for Replicate to use? and if so how can this be done? 

As a test I created a publication and did a full load on a task and some changes too. After checking the database a new publication was created by Replicate.

So why does Replicate create a new one instead of using the existing one and how can I get it to use an exisiting one if possible?

Thanks,

Mohammed

[Heinvandenheuvel]

>>  is it possible to manually create a publication for Replicate to use?

I suspect it is, but WHY?

>> After checking the database a new publication was created by Replicate. So why does Replicate create a new one instead of usi

If you can see that there is a second publication, that implies that Replicate did not 'see' yours. Possibly spelling, possibly ownership. 'We' know how Replicate creates the publications, we have no clue what you actually tried. 

Although I personally think this whole exercise is a waste of time, if you want to explore this further then I suggest you show what you tried in detail (actual commands used) and show the resulting 2 publications.

Good luck,
Hein.