SQL Server - MERGE

[Eduardo_Carrijo]

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.

[Dana_Baldwin]

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

[Eduardo_Carrijo]

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.

[Dana_Baldwin]

@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

[Dana_Baldwin · Accepted solution]

Hi @Eduardo_Carrijo 

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

Thanks,

Dana

[Eduardo_Carrijo]

Hi @Dana_Baldwin 

Yes, they are replicated in the merge.

[Dana_Baldwin]

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

[Eduardo_Carrijo]

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?  

[Dana_Baldwin]

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