RFE: SQL log backups should honor Availability group setting

[johannesk]

Hi,

We backup number of availability group systems. We have a problem with the log backups in such way, that if a previously passive node becomes active, the log backups continue to run on the previously active node that is now passsive. Both nodes are part of the same job as advised by Veeam.
This will generate a message in the job log like this for the active node.

29.4.2016 12:13:21 :: Skipping databases to be backed up from another SQL Server node: db1,db2,db3 etc....

hence, the log backup is now running on the passive node.

The SLA information shows 100% for both nodes now. And point in time restores work fine.

BUT

The availability group setting is configured to accept only backup on primary. And the DB's are not getting aware that the log has been backed up. If I properties the db's on the active node, it tells me the log backup is since the last time the log backup ran on the other node when it was active (now passive).

In my opinion the log backup process should honor the configuration of the availability group that specifies that backups should only run on active node. Also, for the DBA's this is very confusing since they get the impression that backups are not working.

I suggest that this be changed so that the log backups honor the availability group setting. Or explain how this could be the preferred way - which I am totally open for if there is a good argument for it.

[foggy]

Hi Jóhannes, thanks for your feedback. Veeam B&R actually honors the availability group backup preferences: when the logs backup job starts processing a node, it asks SQL whether the node is preferred for the backup, so one of the preferred nodes is always selected. However, this is done once on the job start, so if the primary node changes, Veeam B&R will not know this until the next start of the logs backup job (which occurs with the start of the new session of the corresponding parent backup job) and will continue processing logs on the same node during the next intervals.

[johannesk]

Why do I then get the message:

29.4.2016 12:13:21 :: Skipping databases to be backed up from another SQL Server node: db1,db2,db3 etc....

?

And this message continues even after a full backup has run.

[foggy]

This doesn't look expected to me, you should stop seeing this message on the new active node right after the next parent job session (not active full necessarily, but just the next backup job run, according to the schedule) after the node switch. I will check regarding this, though.

[foggy]

Yes, this behavior is not expected, so if you could open a case for this and provide logs for review, it would be much appreciated.

[johannesk]

I have research this with Veeam support.

So the situation was that both nodes in a two node alwayson system are backed up by the same job. The active node and passive node both get full backup. Then the active node gets a log backup and the logs are truncated. But the second log backup interval, the log backups don't run on the active node any more. The job gives this message (And I think it should be a warning in case of active node).

29.4.2016 12:13:21 :: Skipping databases to be backed up from another SQL Server node: db1,db2,db3 etc....

Thus, now log backups are only running on the passive node with 'copy only' and the logs are note getting truncated. That is considered a breach in SQL administration.

Now I have information about how Veeam evaluates how it does the log backup of the always on system. It is described here:

https://helpcenter.veeam.com/backup/vsp ... pport.html

and information from Veeam support, Veeam evaluates these points and in this order.

1) Required Veeam Backup & Replication components can be installed on this node (the VM must be running).

2) If there are any logs remaining in the temporary folder on the node of AlwaysOn Availability Group, this means these logs were not backed up to the backup repository during the previous session of the transaction log backup job, so this AlwaysOn Availability Group node must be processed first.

3)Databases in the AlwaysOn Availability Group(s) for this node were successfully backed up for the last two processing intervals.

4) Veeam Backup & Replication can establish a network connection to the node (or VIX connection, if connection over network cannot be established).

5) The VM is in the list of preferred nodes for backup retrieved from the Microsoft SQL Server. If there are no preferred nodes, any node can be chosen.

Thus point #5 is evaluated with the least weight. And in our case since there are logs in the temp folder on the secondary node (point #2) the secondary node is chosen to be used for log backups, and the primary not.

What I'm hoping for, is when the next customer experiences this, he will find the documentation explaining it quickly. Also I think that point #5 should be re-evaluated if it should have the least weight. Then I also think that when the message shown above, it should come as a warning or error if it is for the active node, because Logs wont get truncated in that case.

regards,
Johannes

[johannesk]

We have had great success with backing up SQL server 2014 AlwaysOn systems lately. The log backups runs on the primary node as expected. And even if the AG failes over to the secondary node, the backup continues on the node that has been maintaining the log backup circle since last full backup, and truncates the logs. That's awasome.

But now we are starting with SQL server 2016 Availability groups. We configure them the same way as the 2014 AG systems. The AG group is configured with the backup parameter 'prefer primary' (SQL configuration). But still I have not been able to have the backup job that is backing up the pair, do the log backup on the primary node. It always says for the primary node:

12.6.2018 15:22:09 :: Skipping databases to be backed up from another SQL Server node: FINANCE;SAP

Fortunatelly the logs are getting truncated, but not marked as being log backup. And that destroys all backup monitoring for the transaction log backups.

Isn't the SQL backups of SQL Server 2016 AlwaysOn supposed to behave the same as with SQL server 2014 AlwaysOn? Just to make things clear, I'm not talking about basic availability groups (mirroring) nor distributed availability groups (availability group of availability groups.

I have a support ticket on the already #03039467.
Isn't the SQL server 2016 supposed to have the same quality of Veeam Backup as SQL Server 2014 when it comes to Availability groups?