1

enter image description here

I have set my back up preference to primary and the secondary is not readable. Then I did the following:

  1. Set up the same maintenance plan on all replicas in the AG through the maintenance wizard.

  2. Configure the Backup Preference plan is set to Primary on the availability group.

  3. Enable the 'For availability databases, ignore Replica Priority for Backup and Backup on Primary Settings' check box and run your maintenance job.

  4. Now when I ran the Tlog backup on the secondary it generated a Tlog back up which was not supposed to ...

Is this normal?

  • 1
    Can you add screenshots to verify? – Sean Gallardy Sep 14 '18 at 18:14
  • Please find the attached screenshot where i was able to take a tlog back up from db2 where db1 was the primary that time – SQL_NoExpert Sep 14 '18 at 18:34
  • Thank you, unfortunately I only see that a log backup occurred, can't tell for what databases, if those databases are in an AG, what server it was run from, the maintenance plan settings, etc. We'll need to start by knowing what the settings are set that way as double checking against the verbatim. – Sean Gallardy Sep 14 '18 at 18:43
1

If you tick the "For availability databases, ignore replica priority for backup and backup on primary settings" option, then the maintenance plan jobs will perform the backup on a secondary (or any replica the job is executed on).

This option is used to ignore the AUTOMATED_BACKUP_PREFERENCE option for the Availability Group. Since your preference is set to Primary and you don't want backups on the secondary, disable the "For availability databases, ignore replica priority for backup and backup on primary settings" option and your maintenance plan jobs will not perform a backup on the secondary replicas.

Keep in mind that the backup preference option in the Availability Group will not prevent backups from being taken on a secondary. This option simply ensures that sys.fn_hadr_backup_is_preferred_replica will return the correct value, according to the configured preferences, on each replica. It is up to you to ensure this setting is validated and honoured by your backup application.

Maintenance plans generate a T-SQL command under the hood that has an IF statement that checks this function, unless you have enabled "For availability databases, ignore replica priority for backup and backup on primary settings".

Further Info: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/02/maintenance-plan-does-not-backup-database-or-log-of-database-defined-in-availability-group/

P.S. I would recommend taking a look at Ola Hallengren's excellent SQL Maintenance Solution as an alternative to the built-in Maintenance Plans. Ola's solution has built-in intelligence for handling Availability Group databases in accordance with your backup preferences, as well as many other useful features.

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.