Best way to back up databases on a Microsoft SQL Server Availability Group DR Site node

Question

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.

Answer 1

You can use some system dynamic management views (DMVs) and functions in your backup script, for example:

sys.dm_hadr_availability_replica_states

sys.fn_hadr_backup_is_preferred_replica

sys.availability_databases_cluster

sys.dm_database_encryption_keys

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.

Answer 2

Power-shell script will be a very helpful and easy way to do this.

Here is the sample script that will help you to backup availability group database at different backup target, depends on which node is primary.

I'm using dba-tools power-shell commands in this script.

You can install all the modules by simply running Install-Module dbatools in Powershell command line window.

Note: Open Powershell As Administrator (Run As Administrator)

Param
(
[String]$Backup_DG_AG_Listener,     #--------Input Lister Name ex. [String]$Backup_DG_AG_Listener="Listener_1"
[String]$Database,                  #--------Input Database Name ex. [String]$Database="MyDatabase"
[String]$BackupType                 #--------Input Backup type FULL / Diff / Log ex. [String]$BackupType="FULL"
)

#-----------Add Listener_Name and Availability_Group_Name in this table
$AG_Listeners = @{
                    "Listener_1"="Availability_Group_1";
                    "Listener_2"="Availability_Group_2"
                 }

#-----------Add Nodes_Name and Backup Path for the node in this table
$AG_Nodes_Backup_Path = @{
                        "Server_1"="\\MyBackupPath\Production\Server_1\Database_Backup\";
                        "Server_2"="\\MyBackupPath\Production\Server_2\Database_Backup\"
                         }

#--------Get Availability Group name from the hash table $AG_Listeners
$AvailabilityGroup = $AG_Listeners.Get_Item($Backup_DG_AG_Listener)

#--------Get Primary Replica
$PrimaryReplica = Get-DbaAvailabilityGroup -SqlInstance $Backup_DG_AG_Listener | WHERE {$_.Name -eq $AvailabilityGroup} | SELECT PrimaryReplicaServerName

#--------Get Backup Path from the hash table $AG_Nodes_Backup_Path
$BackupPath = $AG_Nodes_Backup_Path.Get_Item($PrimaryReplica.PrimaryReplicaServerName)

Backup-DbaDatabase -SqlInstance $PrimaryReplica.PrimaryReplicaServerName -Database $Database -Path $BackupPath -Type $BackupType -IgnoreFileChecks

Thanks!