Ola Hallengren's backup job related to Availability Groups

Hi Friends,

It's been long time since I have come up with the blog due to several reasons one among them being Corana Virus Pandemic.

Today we will see how to configure Ola's script for Always ON Databases.

As you know the backups would run based on backup preferences . Below is the script of Ola's which needs to be configured if you want to offload your backups. We will talk about the highlighted portion in detail down the line.

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = N'\\XXXXXXX\SQLBACKUP',
@BackupType = 'FULL',
@copyonly='Y'

I have setup where 2 of the replicas are in primary Data center  and the other 2 are in secondary Data center and the secondary Data centers are running in asynchronous mode .

Scenario 1: Backup Preference is running with defaults (Prefer Secondary) and on the primary Replica we have configured the backups with the below Ola Script and on the secondaries it has been configured with the above script(means @copyonly='Y')

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = N'\\XXXXXXX\SQLBACKUP',
@BackupType = 'FULL'

In this case if you run the backup job from the primary replica the backup will happen and the backup file will present in the location specified. What if run the job from any of the other 3 replicas the backup would run successfully on all the replicas but the file would be present only in one of the replicas destination folder.

Why only on one replica? why not on other replicas?

As many of you know the answer will depend on the below function if that returns 1 then the backup will happen.

SELECT sys.fn_hadr_backup_is_preferred_replica ('DBNAME');

Scenario 2: Backup Preference is running with defaults (Prefer Secondary) and on the primary Replica we have  now configured the backups with copy_only option .

Then the backups would run only on the replica where the above function would result 1

Scenario 3: What if you want to run the backups from specified replica instead of depending on the function result?

Simply increase the weight priority of the replica so that on that replica the backups would run.

Conclusion: if you want to run Ola's script irrespective of the fail-over situations always append @copyonly parameter so that it will run as expected. Also kindly verify the function result to avoid any surprises.