Over the last couple of days I’ve been trying to customize Ola Hallegren’s script in order to perform backups on an Always On Availability Group running on Azure (blog to follow soon)[1]. I managed to get everything working apart from the Log Backups and the culprit was fairly easy to find. In Ola’s DatabaseBackup stored proc, there is a section of code that checks whether the specified backup type (full, diff, log) can run on the current server as per below:
IF @Version >= 11 AND @Cluster IS NOT NULL
BEGIN
SELECT @CurrentAvailabilityGroup = availability_groups.name,
@CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc,
@CurrentAvailabilityGroupBackupPreference = UPPER(availability_groups.automated_backup_preference_desc)
FROM sys.databases databases
INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
WHERE databases.name = @CurrentDatabaseName
END
IF @Version >= 11 AND @Cluster IS NOT NULL AND @CurrentAvailabilityGroup IS NOT NULL
BEGIN
SET @CurrentIsPreferredBackupReplica = sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName)
END
The code is pretty straightforward and I would have expected that the following line would return the right result:
- 0: for the primary replica
- 1: for the secondary replica(s) with priorities configured accordingly
However, when I run the following command manually I found to my dismay that both servers returned 0 even though the AG Backup settings had been configured properly:
SELECT sys.fn_hadr_backup_is_preferred_replica('<AnyDBName>')
As it turns out, there is a bug with this function when the server name is defined in lowercase in the AG group. Yes, that’s right! If the servers are defined with lower case letters, the function will always return 0.
My only workaround, not ideal though, was to check for the function result and if that was 0, to check whether the server role is secondary. If that’s true, then I go ahead and execute the backup as per my code below:
IF @Version >= 11 AND @Cluster IS NOT NULL AND @CurrentAvailabilityGroup IS NOT NULL
BEGIN
SELECT @CurrentIsPreferredBackupReplica = sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName)
-- This is required for fixing a bug with sys.fn_hadr_backup_is_preferred_replica() returning always 0 if the server is defined as lowercase in the AG group
IF @CurrentIsPreferredBackupReplica = 0 AND @CurrentAvailabilityGroupRole = 'SECONDARY'
BEGIN
SET @CurrentIsPreferredBackupReplica = 1
END
END
I hope this helps you if you ever come across this issue.
Happy coding...
[1] - If you are NOT using Ola's script for SQL Backups and database maintenance, you are seriously missing out! Get it here