October 25, 2018 at 12:10 pm
I am trying to test the setup of two nodes Alwayson Availability group on two nodes of a windows cluster.
SQL server 2017 enterprise edition for the two nodes.
My question is regarding to setup the backup of the databases for the availability group.
Should I setup maintenance plan on both nodes?
I see when setting up the AG there is a backup references screen, if I chose the second one prefer Secondary, should I only setup maintenance plan on the secondary node or I need to setup MP on both nodes?
Thanks
October 26, 2018 at 6:26 am
Hi,
you should take a look on the page from ola hallengren
https://ola.hallengren.com/sql-server-backup.html
Very good scripts for backups, the scripts know, if the node is the primary or not.
It depends, how you would make your backups, only from the primary, copy_only or log backups from the secondary.
Kind regards,
Andreas
October 27, 2018 at 12:46 am
andreas.kreuzberg - Friday, October 26, 2018 6:26 AMHi,
you should take a look on the page from ola hallengren
I agree 100%, I use Ola's scripts everywhere. To answer your question directly, yes you should setup the backup jobs on both SQL instances. I always create all jobs on all replicas and then add code to any job that runs against an AG database that will determine if the current server is the primary or not. Obviously that code isn't needed in the maintenance plan jobs if you are using SQL Native Maintenance Plans, only if you are using something like the Ola scripts and want the code to be portable between the replicas.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply