asked
Preferred backup strategy for Always On AGs
Do you have a preferred backup strategy for your Always On Availability Group nodes? Do you find that one method is best for performance? Here are some of my thoughts: When I first realized I could do a backup of the secondary node instead of the primary, I was eager. But then I realized that every database in the Availability Group must be in FULL backup mode. Which means that the transaction logs are being used more. And in order to buffer tlog growth and usage, my team likes to do scheduled tlog backups regularly throughout the day. So if I need to do a point in time discovery, I need a FULL backup available plus the tlogs. And that means that I need to do FULL backups on a consistent basis. And we can only do a COPY_ONLY backup on the secondary node. If I did a FULL backup on the primary on a nightly basis, that would defeat the purpose of doing a secondary backup to reduce resource contention on the primary. And if I did a FULL backup once a week and then had to do a point in time recovery, I would have to get the FULL backup plus a bunch of TLOG files to do the restore. I am doing some testing now, but I am trying to see if doing TLOG backups on the secondary improves the performance on the primary. I suppose that if my focus is to have performance over a quick restore, then just doing a COPY_ONLY backup on the secondary is the way to go. But if having a point in time backup available is more important than performance, then I would do FULL backups on the primary on a daily basis. Thanks in advance, Greg
If you're not doing DIFF backups, then there's no problem using FULL COPY_ONLY and TLOGs to restore...
Thank you Thomas! I just tested that successfully. I think I know what our backup strategy will be now.
This might be slamming into an open door, but I add it just as a precaution: When planning to do backups on secondary replica, you should plan for not Always knowing which instance holds the secondary replica (since the AG could have failed over). When configuring the AG, set AUTOMATED_BACKUP_PREFERENCE to SECONDARY (this is default). Create and schedule the backup job on every node in the AG and have the jobsteps check: IF sys.fn_hadr_backup_is_preferred_replica ('Name of Database to backup') = 1 BEGIN --Perform Full CopyOnly or Transaction Log Backup END That way your AG will tell the job if it is currently the preferred node to take backups on or not.
One thing to note is that offloading your backups to your secondaries does require those secondaries to be fully licensed. Just because a database is in an AG and in full recovery does not mean the log file gets used more. It will be used just as much as it would be in simple recovery. In full recovery mode it simply means log management is no longer handled just by SQL Server, your backup design comes into play to ensure the log size is managed and the VLFs are reused to not require excessive growth. In synchronous mode though the AG would have those VLFs active until the transaction is committed on all the secondaries in that mode. In an AG configuration the only backup you can't do on the secondaries is a differential. However the main culprit in performance around performing backups is generally always the full backup. Performing differential or transaction log backups generally do not cause an excessive amount of performance issues, outside of just IO activity. It would strictly be based on how much it has to backup. I have many high traffic systems doing full weekly or daily, differentials a few times during the day or just daily, and then transaction log backups every 15 minutes or less. If you chose to do full, copy-only backups and then transaction log backups (don't support copy-only) on your secondaries you can utility [dbatools](https://dbatools.io) to help with generating or even performing the necessary restores. You can use `Restore-DbaDatabase` and just point it at the directory where your backup files are, and it can pull the backups needed to restore the database...or you can have it just output the T-SQL for you.
17 People are following this question.
