I have an Availability Group (Primary only is readable) with 2 nodes locally and 1 node at a DR site. Both sites have a backup system. Locally, I’m using the local backup system to back up the AG data bases on the Primary node. But what is the best strategy for backing up the AG data bases when the primary node fails over to the DR site? Use the backups system at the DR site? Use the backup system from the local site (if the data center hasn’t been hit by an asteroid)? Both? Something else? And for System data bases (not in the Availability Group)?
My concerns are having local backups at the DR site (in case the other site is hit) vs. keeping the log chain intact to avoid full backups unnecessarily (1 of our data bases is almost a TB).
Thanks for any help or advice.
Best Answer
You can use some system dynamic management views (DMVs) and functions in your backup script, for example:
to determine the current node's assignment (primary, secondary, etc) and direct where the full/diff/t-log backups should be coming from. If you use that in conjunction with a file share (as @Steve mentioned), then all backups can be pointed to the same target. You can get creative from there (eg, copying the backup files from the share to another site) to bolster for DR purposes, etc.
You may also glean some good script setup insights from Ola Hallengren's backup scripts (https://ola.hallengren.com) which take AGs into consideration.
Hopefully that helps some.