topic Re: SQL Server Publication/Article Question in Qlik Replicate

SQL Server Publication/Article Question

PGN — Wed, 08 Jun 2022 14:35:21 GMT

I have a clarification question on SQL Server Publications/Articles. Most of our transactional task endpoints are configured to read changes (change processing mode) as "Prioritize Online Logs." We now have a server/database that is using a 3rd-party backup software and we do not have access to the tlog backup files, so we decided to use Online Logs Only. I'm guessing the risk here is that if the task fails or is stopped for more than the duration of when the tlog backups happen, we would need to reload the tables or provide the tlog files locally and change the change processing mode to be able to read both online and backup files. However, I'm thinking we'd have to manually manipulate MSDB to tell Replicate where the backup files are...so maybe that's not even an option.

But my other question is, I noticed with Online Logs Only, no articles are created, just the publication. I know from past experience that Replicate creates the publications and articles as a "placeholder." So I'm wondering if the selection of Online Logs Only changes that functionality to just create the Publication and not the articles.

Thanks.

Re: SQL Server Publication/Article Question

Dimitri_Remez — Fri, 10 Jun 2022 06:57:20 GMT

Hi Paul,

Thank you for your questions!

In relation to the first part of your post, you may specify the location of the third-party backup logs in the SQL Server endpoint at the Alternate backup folder. Please, keep in mind that you'll have to use the Microsoft SQL Server native format of the backup files. There is no manipulation required on the MSDB side.

As another option, you may use MS-CDC which doesn't require the use of T-Logs.

When it comes to the second question, we would suggest opening a support ticket, attaching the Diagnostics Package and the task logs with the SOURCE_CAPTURE component set to Verbose logging level so that we can look into this for you.

Please, have a look below at the documentation with some useful information in relation to the above points:

Using Microsoft SQL Server as a source -> Setting advanced connection properties: https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/SQL%20Server/SQLServerDBSource_AdvProps.htm#ar_sqlserverds_1727505754_1669928
MS-CDC:
- https://community.qlik.com/t5/Knowledge/SQL-Server-Replication-Enable-CDC/ta-p/1752142
- https://help.qlik.com/en-US/replicate/November2021/pdf/Replicate-Setup-and-User-Guide.pdf

Thank you!

Dimitri

Re: SQL Server Publication/Article Question

lyka — Fri, 10 Jun 2022 16:28:53 GMT

Hello,

If you will use online logs only then its recommended to use sp_repldone to avoid premature deletion of tlogs

Thanks
Lyka

Re: SQL Server Publication/Article Question

PGN — Mon, 13 Jun 2022 11:30:23 GMT

Thanks. I hadn't thought of the Alternate backup folder option.

We thought about using MS CDC, but the application that's consuming the changes was written with the Replicate CDC tables as their source, so they may have to rewrite part of their code.

Re: SQL Server Publication/Article Question

PGN — Mon, 13 Jun 2022 11:32:29 GMT

Thanks for the reply. We'd like to avoid holding up the commits, and, as we experienced this past weekend, if the task fails in an off-hour, we would need to go back to the t-log backups anyway.

Re: SQL Server Publication/Article Question

lyka — Tue, 14 Jun 2022 14:50:45 GMT

Hello,

Thank you for your reply. we suggest using sp_repldone with online logs only. if you notice, there's warning on the task log related to this. Anyway, let us know which approach and solution work for you and if you will require further assistance, we are here to help!

Thanks

Lyka