Windows Cluster with SQL Transactional Backups Running Long

[AndrewTobin]

Hey guys,

We've recently started using Veeam for our backups, and I noticed an issue I would appreciate some guidance on.

Scenario: Windows Cluster running SQL Servers - with multiple instances on each node. When index maintenance is run on the servers the transaction log grows larger than usual, causing "misses" on the transaction log schedule. For instance, last night I had 3 misses on one node of 20 minutes, 1 hour, and 1 hour, as it was backing up one Sql Server instance that had index maintenance.

It is my belief, from reading the statistics and the report, that when it has a miss like this, that it is taking longer to backup a single large transaction log, and that all Sql Server instances on that node are no longer being backed up on the regular interval, while that single log is being backed up.

This gives me hesitation, because if I was backing up each server using regular Sql Backups, they'd be working independently and the growth of each server wouldn't interfere with the backup schedule of the others.

Is there a way to mitigate this, so the other Sql Instances still receive a regular transaction log backup in the background while that long running instance continues?

I have read in this whitepaper that Veeam uses an actual Sql Server backup command to perform the backup (as an example):

Code: Select all

DECLARE @database_name_var NVARCHAR(255) 
SET @database_name_var = N'Tandlaege' 
BACKUP LOG @database_name_var TO DISK = N'C:\ProgramData\Veeam\Backup\SqlLogBackup\{78d18633-05ae-4613-b903-b2ea8854ad34}.bak'

If I were to turn off transaction logging via Veeam, can I set up a schedule transaction log backup to that directory and will Veeam then process the logs received, or is there another way I could manually create transaction logs into Veeam?

I would love to have the point-in-time restore, and all the other benefits off Veeam, but I am concerned that a single instance on a node will prevent the other instances from having their transaction logs backed up for an extended period.

[Vitaliy S.]

Hi Andrew,

If I were to turn off transaction logging via Veeam, can I set up a schedule transaction log backup to that directory and will Veeam then process the logs received, or is there another way I could manually create transaction logs into Veeam?

Unfortunately, there is no other way to "feed" transaction logs into Veeam other than using Veeam-native backup jobs.

As for the mitigation question, then it's hard to say right now. Do you see those "misses" every time the maintenance task kicks in?

Thanks!

[AndrewTobin]

Thanks Vitaliy,

We've only been using it for the cluster since Friday night (tonight is Sunday), but we had misses on Friday and Saturday (1 miss, 35 minutes).

The problem to my mind is that the other instances on the node are no longer being backed up while this database is holding the current backup. This means that we have had growth in log sizes on the other databases, on other instances on the node.

The things I would wish to be changed are:
a) the instances of a node were iterated separately, instead of just the nodes
b) I could exclude a database, or server from those having transaction log backups, and/or,
c) I could trigger a backup to that directory, using a similar command, which then Veeam would pick up from the directory - so I could manually trigger a backup if its delayed.

I would be interested how larger clients with many more servers handle this, because it seems problematic.