SQL Server Publication/Article Question

[PGN]

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.

[Dimitri_Remez · Accepted solution]

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_...
• 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

[lyka]

Hello,

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

Thanks
Lyka

[PGN]

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.

[PGN]

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.

[lyka]

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