1

Just wondering what is the best practice for backing up system databases in Availability Group i.e should it be backed up in Primary, secondary or in all replicas please. Thanks in advance

2

Since they are independent of each other, backup all system databases, for each individual instance hosting a replica.

2

TLDR; if you can do your backups on your secondary, run them there, but system databases need to be done per server.

Our main consideration for this is licensing, if your secondary node is passive then you can't take backups on it, if you're using it as a read replica then that's not an issue.

Second consideration is what synchronisation method are you using if your actually in synchronous mode then your secondary node is up to date as the data is written at the same time.

So if you're on an active secondary with synchronous commit then you can happily run the backup on the secondary node to release pressure on the primary node.

The AG group is sensible enough to tell the Primary replica that the backup has been taken and will clear the transaction log out on the primary accordingly (all of our backups are done on secondary) hereby no backups never need to be done on the primary node.

Any databases that aren't in the AG (aka master, model, msdb and any other locals) need to be backed up independently still

  • good shout, done – Ste Bov Jun 27 '17 at 13:11

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.