Replication with Veeam Backups for SQLServer

[RonFusionHSLLC]

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

[Heinvandenheuvel]

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.

[Heinvandenheuvel]

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

[Heinvandenheuvel]

- 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! 

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

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.

[RonFusionHSLLC]

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.

rm

[Heinvandenheuvel]

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-trunca... )

hth,

Hein

[RonFusionHSLLC]

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.

[RonFusionHSLLC]

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

[Heinvandenheuvel]

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

[lyka]

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