question

ajain87 avatar image
ajain87 asked

Backup Scenarios in Always ON

I have always on availability replica setup, Primary replica A and Secondary Replica B. Now following are scenarios: 1. Have set backup preference "secondary only" then how will I recover my database in disaster from backup files because secondary replica support COPY_ONLY feature and normal TLOG backups. 2. Have set backup preference "Primary", Normal backups going on and suddenly failover happens from Replica A to Replica B. Now where should backup job run and how it is work? Do we need to create a backup job on both replica or backup job switch between Replicas?
sql-serverbackupclusteringavailability-groups
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
anthony.green avatar image
anthony.green answered
You will need to setup jobs on all nodes of the cluster and use something like fn_hadr_group_is_primary to find out if the server is the primary to do the full backup. You will need to ensure if your backing up locally that you can get to each nodes backup areas to build the full filelist to do a restore, or backup to a central area where all nodes can write full and log backups http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-7-detecting-primary-replica-ownership
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Anthony.Green for answering the question. What my understanding as per your suggestion is we have to perform manual task like creating function or create backup jobs in each nodes. And we need to traverse all the nodes for files if we don't have central location of Backup directory.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.