No Log Backups are taken when Backup Preference is set to "Any Replica"(NONE)

[JacobHorwath]

When using the DatabaseBackup solution in an AAG scenario, and the Backup Preference option is set to "Any Replica" (i.e. AUTOMATED_BACKUP_PREFERENCE = NONE) no transaction log backups are taken.

There is an option to "OverrideBackupPreference". However, logically this does not make sense to need to override the setting in this case. As the setting itself implies a backup will be taken from any replica. When there is no preference set, all replicas should be an acceptable "preferred backup replica". See below for example output from the script in this scenario:

Using this command:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'AVAILABILITY_GROUP_DATABASES',
@Directory = '\\Server\Backup\',
@BackupType = 'LOG'

When Backup Preference set to Any Replica (NONE):

Date and time: 2018-09-07 10:22:06 Database: [SQLdmRepository] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Encrypted: No Availability group: AAG Availability group role: PRIMARY Availability group backup preference: NONE Is preferred backup replica: No

Versus when the backup preference is set to PRIMARY:

Date and time: 2018-09-07 10:23:46 Database: [SQLdmRepository] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Encrypted: No Availability group: AAG Availability group role: PRIMARY Availability group backup preference: PRIMARY Is preferred backup replica: Yes

In the second case, the command runs and a transaction log backup is created.

Command: BACKUP LOG [SQLdmRepository] TO DISK = N'\Server\Backup\AAG_SQLdmRepository_LOG_20180907_102346.trn' WITH NO_CHECKSUM, COMPRESSION

[olahallengren]

Which version is this on? Could you do a SELECT @@VERSION.

[olahallengren]

One question is how sys.fn_hadr_backup_is_preferred_replica is working when AUTOMATED_BACKUP_PREFERENCE = NONE.

Could you try executing this on each of your replicas:

SELECT sys.fn_hadr_backup_is_preferred_replica('SQLdmRepository')

[JacobHorwath]

Hi Ola,

To answer your questions.

1) Here is the SQL Server version:

Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (X64)

2) If I change the preference to NONE and run

_SELECT sys.fn_hadr_backup_is_preferredreplica('SQLdmRepository')

on all of 3 of my replicas. I get these results:

AAG-LISTENER = 0 SQL-AAG-01 = 0 SQL-AAG-02 = 1 SQL-AAG-03 = 0

Seems like the issue is backup_priority in sys.availability_replicas? It is set highest for server 02.

I would assume that choosing "Backups can occur on any replica in the group" would mean that I should be able to execute a backup on any replica.

I would not assume that the "backup_priority" setting would take full precedence over the "automated_backup_preference" setting.

Of course, it's your call. :)

[olahallengren]

As I understand it, sys.fn_hadr_backup_is_preferred_replica should always return 1 on one and only one replica. This is useful as you only want to perform a backup on one replica at the same time.

It looks like that is how it is working also when AUTOMATED_BACKUP_PREFERENCE = NONE.

How should DatabaseBackup work in this scenario? I think that it should follow sys.fn_hadr_backup_is_preferred_replica as it currently does.

[JacobHorwath]

You are right Ola. This is probably an edge case where we're trying to prevent a backup happening on a specific instance. I understand how you're using the function. It does make sense. Since we're obviously swimming against the current, I don't think it makes sense to modify the functionality of the solution here. Thanks for your work.