0

Can someone kindly explain this to me please. My understanding is that we can not run differential backups on the secondary replica of an AG, so if the business requirements are such that differential backups are required, then we can’t choose our backup preference as secondary.

However, the legacy system for the system I'm working on, runs differential backups on the AG databases and the backup preference is set to 'Prefer Secondary'. The SQL version on the legacy system is SQL 2016 SP3 and the new one I'm working on is SQL 2019.

Thank you

1 Answer 1

1

My understanding is that we can not run differential backups on the secondary replica of an AG, so if the business requirements are such that differential backups are required, then we can’t choose our backup preference as secondary.

Correct.

However, the legacy system for the system I'm working on, runs differential backups on the AG databases and the backup preference is set to 'Prefer Secondary'.

If backups are running then they may not be using the built in functions to check for preferred replicas, they aren't taking differential backups, they aren't checking to see if the job/query/whatever is running properly, some combination of those.

4
  • thank you for your response and all your help in increasing my understanding of AGs, very much appreciated. The ola.hallengren backup scripts were used in the legacy system and that's what I'm to use for my new system too. From what I see on the OH site, the DatabaseBackup stored procedure uses the SQL Server BACKUP command. I would have thought then that if it's an AG then it will check the backup preference? or am I wrong?
    – PTL_SQL
    Commented Feb 18, 2023 at 6:42
  • I think I got to the bottom of this mystery :-) The backup preference is definitely set to 'Prefer secondary' but on looking at the backup folders, the backups actually happen on the primary. I also had a look at the OH DatabaseBackup stored procedure and can see that it does check for the backup preference. What I'm not sure about is that 'overridebackuppreference' is set to 'N', but the backup actually happens on the primary not the secondary. So I'm not clear how it does it but the backup files are on the primary replica. I'd appreciate if you have any more thoughts. Thank you
    – PTL_SQL
    Commented Feb 18, 2023 at 12:15
  • @PTL_SQL The backup command has no knowledge of AGs. You must use the sys.fn_hadr_backup_is_preferred_replica function to check if that replica is the preferred place or not and if it is, take a backup, otherwise do not. Commented Feb 18, 2023 at 14:12
  • thanks again for all your help and time, very much appreciated.
    – PTL_SQL
    Commented Feb 20, 2023 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.