SQL source missing records

[toni_lajsner]

We have noticed that multiple records from source sql are not moved to our target destination.

Only error massage is following: No FULL database backup found (under the 'FULL' recovery model). To enable all changes to be captured, you must perform a full database backup.

But we notice also that our latency is extremely high, and it can reach 12-hour mark.  
Can full database backup solve our CDC problems or performance adjustment can be done on to Qlik to solve this issue that we are facing. 

[OritA]

Hi Toni,

In order to start capturing CDC, a full database backup should be performed at least once. If after you perform this you still face issues, then we would have to review your endpoint setting. For this purpose you may open a case, describe the problem and attach the task diagnostic package to the case so we can review it setting and see what is causing the problem.

Thanks & regards,

Orit

[Bill_Steinagle]

Hi,

Thank you for the post to the QDI Forums. As per Orit's update you also need to check on the SQL Server Source the screen share below as these settings control how often the TLOG is truncated and if Replicate will access the TLOG online or Backups or both. I would check the Change processing mode (read changes from) setting. If this is not set correctly for the Source this can cause data to be missed as the TLOG would be truncated over a period depending on the truncation set and these settings.

• Change processing mode: Choose one of the following change processing modes:

  • Prioritize Online Logs - This is the default. Replicate will first look for the changes in the online transaction logs. If the changes cannot be found in the online transaction logs, it will look for them in the backup transaction logs instead.

  • Prioritize Backup Logs - When this option is enabled, Replicate will first look for the changes in the backup transaction logs. This can improve performance when reading from the online transaction log is slow (e.g due to lock contention) or when using file-level access to access the backup transaction logs.

    If the changes cannot be found in the backup transaction logs, it will look for them in the online transaction logs instead.

  • Backup Logs Only - When this option is selected, Qlik Replicate will try and find the changes in the backup transaction logs only. Selecting this method results in increased latency due to the interval between backups. The actual latency time will remain constant, but will vary according to the backup schedule.
  • Online Logs Only - When this option is selected, Qlik Replicate will try and find the changes in the online transaction logs only.

See link below for more details on SQL Server and change processing modes:

https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/SQL%20Server/SQLServerDBSource_...

Thanks!

Bill

[SwathiPulagam · Accepted solution]

Hi @toni_lajsner ,

For Replicate CDC, you have to take the Full database backup which helps to suppress the warning message.

But for Latency, you have to schedule a frequent Transactional log backup.  It will help to maintain a consistent LDF file size.

Thanks,

Swathi

[toni_lajsner]

Hi Swatli 

Yesterday we had a meeting with our source and they have showed me both Full database backup that is running on daily basis and also frequent transaction logs as well.

What can be the next step to investigate this issue ?

//Toni 

[Dana_Baldwin]

Hi @toni_lajsner 

At this point it is probably best to open a support case and attach a diagnostics package for the task so we can take a deeper look.

Thanks!

Dana

[Heinvandenheuvel]

Let me start out by saying that I do not really expect the issue to be caused by the indicated lack of Full Backup, but it could be so let's try to understand that warning to rule it out.

Maybe your DBA and Replicate understand a Full DB backup to be different things.

To find out what Replicate is looking for please Enable Logging SOURCE_CAPTURE VERBOSE for a TEST TASK  and start and and stop it. You'll find a statement similar to:

SELECT count(*) FROM msdb.dbo.backupset s 
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() AND s.[type]='D'
AND s.recovery_model in ('FULL','BULK-LOGGED')

Now go ahead and execute that statement against the source DB. You'll need it for your support case anyway :-).

Replicate insist that the count is 1 or more as the belief is that if no full backup was made then MS SQL has no reason to make faithful LOG entries (as there is no base) and can 'walk away' from them under pressure. I've found MS SQL statements to that effect in the past, but cannot quickly find them back. It are softly implied by: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/transaction-log-backups-sq...

Replicate warns the user if it does not believe SQL server logs are safe but continuous to work as ever. If it thinks it is safe then it will indicate: "Database 'FULL' backup fitness approved."

Now Replicate doesn't actually care whether the backup chain is in tact. It just wants to make sure MS SQL makes solid logs all the time. Therefor  Replicate will even be satisfied with a full backup to NUL (almost \dev\null in Linux) weven though we all know that a backup to the bitbucket cannot be used for restore basis. That may sound like Smoke and Mirrors - but trust me, it works. Have your DBA run a full backup to NUL and make the message go away, and hopefully - but unlikely - make the issue go away .

hth,

Hein