topic Replication with Veeam Backups for SQLServer in Qlik Replicate

Replication with Veeam Backups for SQLServer

RonFusionHSLLC — Mon, 24 Jan 2022 19:29:16 GMT

I'm seeing warnings and failures during our Veeam backup operations (sqlserver). I'm guessing Replicate has issues when Veeam truncates the log files as part of its backup strategy. What is the best way to configure replication when using a third party backup solution such as Veeam?

During the backups we get these a a few other errors/warnins...

mssql_approve_table_full_logging_setup(...) failed in checking PK presence for table identified by ID '1954106002'

LSN '001f611b:00003584:0003' could not be retrieved as it is located in the backup logs, while the endpoint is configured to access the online logs only

Task 'Production PHI Replication' encountered a recoverable error

Re: Replication with Veeam Backups for SQLServer

Heinvandenheuvel — Tue, 25 Jan 2022 21:42:10 GMT

update: " I edited away some some double angle bracket I had used to indicate quote from OP, replacing them with a dash and red color. Also changed some numeric values and little things and now it seems to take. Hmmm"

I submitted a reply to this topic twice but it did not posts or perhaps were deleted instantaneously without a message. Dis I use a bad word? Let's see if this gets in. It's odd, as I submitted several other replies to other topics and they all took just fine.

Hein.

Re: Replication with Veeam Backups for SQLServer

Heinvandenheuvel — Tue, 25 Jan 2022 21:11:15 GMT

>> Veeam truncates the log files as part of its backup strategy.
For real? That's a corruption strategy, not a backup strategy. Such backup would not be useful for SQLserver itself, nor for Replicate. IMHO it is cleaner, clearer, to just delete versus truncate as it might as well not be there at all.

>> What is the best way to configure replication when using a third party backup solution such as Veeam?

Ask the third party for support, or money back!

Replicate is willing to help some with the 'advanced' endpoint options like 'Select virtual backup device types' and 'Backup folder preprocessing command:' but ultimately it is dependent its environment to restore a valid archive log.

>>> mssql_approve_table_full_logging_setup(...) failed in checking PK presence for table identified by ID '1954106002'
That's possible a table which was deleted before the archive log was processed. Could be?

>> LSN '001f611b:00003584:0003' could not be retrieved as it is located in the backup logs, while the endpoint is configured to access the online logs only
That's likely the consequence of a truncation.

hth,

Hein.

Re: Replication with Veeam Backups for SQLServer

Heinvandenheuvel — Tue, 25 Jan 2022 21:15:32 GMT

- Veeam truncates the log files as part of its backup strategy.
For real? That's a corruption strategy, not a back strategy. Such backup would not be useful for SQLserver itself, nor for Replicate. IMHO it is cleaner, clearer, to just delete versus truncate as it might as well not be there at all.

- What is the best way to configure replication when using a third party backup solution such as Veeam?

Ask the third party for support, or money back!

- mssql_approve_table_full_logging_setup failed in checking PK presence for table identified by ID

That's possible a table which was deleted before the archive log was processed. Could be?

- LSN 'xxx' could not be retrieved as it is located in the backup logs, while the endpoint is configured to access

That's likely the consequence of a truncation.

hth,

Hein.

Re: Replication with Veeam Backups for SQLServer

RonFusionHSLLC — Thu, 27 Jan 2022 11:49:51 GMT

That kinda helps, but usually products such as this have specific reasons for generating errors, and some advice for how to solve.

Given Veeam has been (not sure it is today) one of the go-to choices for VMWare backups, I would have thought the good people at Qlik would have a best practice guide when something like Veeam is involved.

For that matter, any documentation on what exactly to do in a common situation, such as a backup is running in the middle of a near real-time replication.

I'll probably just log a ticket with support.

Re: Replication with Veeam Backups for SQLServer

Heinvandenheuvel — Wed, 02 Feb 2022 17:17:01 GMT

Ok, so I could not believe a backup product would truncate a log and googled. Found this:

https://helpcenter.veeam.com/docs/agentforwindows/userguide/transaction_truncation.html?ver=50

So it is not the backed-up file that is truncated but Veeams telling MSsql it can safely truncate the log. One thing to check for the SQLserver source endpoint in Replicate is the "Exclusively use sp_repldone within a single task" setting. That's probably not going to work with that.

You may want to figure out how to make Replicate read the Tlog backup as made by Veeams probably using the "Backup folder preprocessing command:" option. I know that's needed for Quest "LiteSpeed". They can also truncate the Tlog ( https://support.quest.com/litespeed-for-sql-server/kb/25570/does-litespeed-log-backup-process-truncate-transaction-log-file-similarly-to-native-sql-server )

hth,

Hein

Re: Replication with Veeam Backups for SQLServer

RonFusionHSLLC — Thu, 03 Feb 2022 13:46:18 GMT

Thanks for the advice/clues Hein, there is very little on the subject. We have a session with Qlik consulting today, hopefully we can find some documentation on integrating stuff like Veeam with Qlik. I'll post the update after we find the solution.

Re: Replication with Veeam Backups for SQLServer

RonFusionHSLLC — Wed, 16 Feb 2022 15:14:51 GMT

Testing now...as I understand it, this setup reads tlog first, then hunts down everything else from tlog backups (as needed).

SS DB set to Full recovery

Veeam full backup once per day (retention 7 days)

Veeam transaction log backups every 30mins - retain logs until corresponding image-level backup is deleted

Replication - is setup

start transactions in database
Alternative backup folder (shared folder where veeam stores backups)
Replication has access to backup log files - checked
Backup folder (shared folder where veeam stores backups)
password - as required
Delete processed backup logs - un-checked
Select virtual backup devices - un-checked
Internal params
- suspendtablewithcomputercolumn - checked
- TlogAccessMode - preferr tlog

Re: Replication with Veeam Backups for SQLServer

Heinvandenheuvel — Wed, 16 Feb 2022 15:35:20 GMT

Thanks for the detailed setup description which helps to avoid seemingly silly questions.

I had expected you to need to check "virtual backup devices". As per User Guide:

"Select virtual backup device types: When this option is selected, Qlik Replicate will read changes
from the specified virtual device(s). Usually, this option only needs to be enabled when using a thirdparty
backup utility (which will be recorded as a virtual device)."

Also, on the access order, that Tlog is preferredis of course only possible when the log records are still there based on the time boundary. If the Replicate source reader fell behind (for example after an intense re-org generating lots of irrelevant for Replicate log data) or when a task is started by timestamp it will read Archive Logs first until and will read it to the end. No chunking as for the Tlog. This until when looking for the next log it reaches a time where the data is also in the Tlog.

Hein

Re: Replication with Veeam Backups for SQLServer

lyka — Wed, 16 Feb 2022 23:51:56 GMT

Hello,

Virtual log backups are usually done by 3rd party SQL server backup program , like Litespeed, Red gate etc. or by other 3rd party software like Symantec NetBackup.

Please note that Replicate , by default is not reading such backup logs, and so we give the warning that such virtual backups exist.

If you have such virtual backups, but they are not related to your regular transaction log backup process, and those backups don’t contain any changes that are relevant to replication, or they are done as "COPY_ONLY",
Then you can ignore this warning.

If you are using virtual backups as your regular backup option for SQL Server, then you will need to do both steps:

1. Mark the "select virtual backup device types" in the SQL server source database settings in Replicate->Advanced tab
2. Note that doing (1) will cause Replicate to try and read those virtual backup logs , but since replicate can't read 3rd party backup logs, you will need to provide to Replicate a conversion utility, in order to convert each such tlog to a native SQL server format. We have such example for Litespeed, but for other programs , you will need to work out the conversion script.

Starting 2021.11 release, there is a MS-CDC source endpoint which elimiates the need to rely on the transaction log . This minimizes the risk of data loss resulting from online transaction log retention time and backup transaction log inaccessibility.

Thanks

Lyka

Re: Replication with Veeam Backups for SQLServer

KevinChiu — Thu, 18 Aug 2022 19:37:12 GMT

So we are in the same situation, and we don't find that Veeam and Qlik can live in harmony.

For us, it seems that everytime Veeam does a backup and marks that SQL server msdb.dbo.backupset to say it did a backup, it loses it's LSN and if your task was stopped, we have troubles resuming it cause it can't find the LSN point cause VEEAM had the last entry and changed the LSN.

For the original poster, are you able to get VEEAM and QLIK working well together?

Re: Replication with Veeam Backups for SQLServer

KevinChiu — Thu, 18 Aug 2022 19:40:31 GMT

Also of note, we stopped the Veeam Transaction log backup, it's set to Copy_Only

Re: Replication with Veeam Backups for SQLServer

KevinChiu — Thu, 18 Aug 2022 19:48:07 GMT

I guess my simple question can QLIK task stop have a VEEAM backup happen which changes the LSN record and then have QLIK resume the task?

From what we have seen, this can't happen. Which is kind of a problem cause we use VEEAM for DR and it does a 4 hour log copy, which means if we had QLIK task down for any amount of time within that 4 hour window, the QLIK task won't start.

Re: Replication with Veeam Backups for SQLServer

Michael_Litz — Thu, 18 Aug 2022 19:56:09 GMT

Hi Ron,

You could try the relatively new end point called Microsoft Azure SQL (MS-CDC), it will work with regular MS SQL.

This end point does not need access to the TLOG backups in any way so it would not matter what third party tool you use for that.

It will rely on using the MS-CDC that you configure inside of SQL.

Thanks,
Michael