2

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.

  • First as a point of clarification - nodes and replicas are two different concepts. Nodes are cluster objects representing Windows instances available for failover, whereas replicas are AG copies available for failover. I'm assuming you are referencing replicas in this question. Either way, ideally, you want your backups available somewhere other than the AG. When last I worked with AGs I set up local backups for all replicas and the primary backups were copied off to file server. In that environment we didn't have tape backups and redundancy was required. – Steve Mangiameli Jul 14 '15 at 17:48
  • It's also worth mentioning that you can (should) configure full and transactional backups accordingly to mitigate the need for multiple full copies. Our AG had weekly full backups, nightly diffs and transactionals every 15 minutes. It's easy enough to script out the restore if your file names include some kind of incremental key, like a timestamp. – Steve Mangiameli Jul 14 '15 at 17:51
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.

0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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