topic Re: SQL Server - MERGE in Qlik Replicate

SQL Server - MERGE

Eduardo_Carrijo — Wed, 08 May 2024 14:24:33 GMT

I have a SQL Server database that has merge replication configured with another SQL Server, I need to replicate some tables using Qlik Replicate, we know that CDC cannot be activated, I would like to know what the restrictions are in relation to transactional replication, if This impacts merge replication that already exists, if Qlik uses other logs than the one already being used by MERGE.

Re: SQL Server - MERGE

Dana_Baldwin — Wed, 08 May 2024 22:42:47 GMT

Hi @Eduardo_Carrijo

I don't have complete information regarding your question, but I can say that either MS-Replication needs to be enabled or MS-CDC for change processing. Is this a one-time need, full load only?

MS-Replication:

Prerequisites | Qlik Replicate Help

MS-CDC:

Prerequisites | Qlik Replicate Help

Re: SQL Server - MERGE

Eduardo_Carrijo — Wed, 08 May 2024 23:03:54 GMT

MS-Replication is enabled and configured for SQL Server merge replication, and as the database is configured for merge replication with another SQL Server instance, it is not possible to activate CDC, my question is whether to replicate using MS- Native replication of SQL Server, will Qlik use the same log files as MERGE? I need to be sure, because it's a production database.

Re: SQL Server - MERGE

Dana_Baldwin — Wed, 08 May 2024 23:52:38 GMT

@Eduardo_Carrijo Yes Qlik Replicate will read from the same transaction logs. I'm checking into this to see if there is any anticipated impact.

Thanks,

Dana

Re: SQL Server - MERGE

Dana_Baldwin — Thu, 09 May 2024 00:04:50 GMT

Hi @Eduardo_Carrijo

Are the tables that you want Replicate to handle already part of the Merge replication?

Thanks,

Dana

Re: SQL Server - MERGE

Eduardo_Carrijo — Fri, 10 May 2024 12:54:26 GMT

Hi @Dana_Baldwin

Yes, they are replicated in the merge.

Re: SQL Server - MERGE

Dana_Baldwin — Fri, 10 May 2024 16:49:02 GMT

Hi @Eduardo_Carrijo

Should work fine but you need to set this internal parameter on the source endpoint, advanced tab:

ignoreMsReplicationEnablement

This will tell Replicate not to create a new publication for the task.

In the search parameter field, type in an exclamation point. Scroll down and select the parameter. Enable the check box. Click OK to save your edit and close the window, then click Save on the endpoint. While not relevant to this specific issue - tasks that use this endpoint will need to be restarted before the parameter goes into effect (endpoint settings are only read & processed during task startup).

Thanks,

Dana

Re: SQL Server - MERGE

Eduardo_Carrijo — Tue, 11 Jun 2024 14:27:19 GMT

I need some more information to be able to proceed with this replication, I can't take any risks.

Does Qlik still open and hold the oldest trx in the database, and does Qlik still need Safeguard latching entries with their share db lacks? Does Qlik still need local tlog backups to recover?

Re: SQL Server - MERGE

Dana_Baldwin — Tue, 11 Jun 2024 18:43:04 GMT

Hi @Eduardo_Carrijo

By default, the source SQL Server endpoint will use transactions as a method to try and keep changes in the online transaction log.

Also by default, the source SQL Server endpoint will "prefer" the online log but will attempt to read from the backup TLOG if the LSN it needs is no longer in the online log.

The backup TLOG has to be in native format to be read, but some backup software providers have a command line utility that can be specified to decompress/decrypt the file when needed.

There are several possible permutations on the above scenarios based on a number of things. It would be difficult to attempt to document each one here. I suggest you consider working with our Professional Services team (fee based) for the right solution for your environment. Please reach out to your Account Manager to initiate this engagement.

Thanks,

Dana