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?
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
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.
Correct. Please find more reading on the topic on these links
http://blogs.msdn.com/b/sqlgardner/archive/2012/07/18/sql-2012-alwayson-and-backups-part-1-offloading-the-work-to-a-replica.aspx
http://blogs.msdn.com/b/sqlgardner/archive/2012/07/21/sql-2012-alwayson-and-backups-part-2-configuring-backup-preferences-and-automating-backups.aspx
http://blogs.msdn.com/b/sqlgardner/archive/2012/08/28/sql-2012-alwayson-and-backups-part-3-restore.aspx
18 People are following this question.
