Maintenance Plan Does not Backup Database or Log of Database Defined in Availability Group


Maintenance plans can be created in SQL Server Management Studio to schedule periodic database and log backups of critical data. SQL Server maintenance plans are AlwaysOn availability group aware and special considerations must be made when defining a maintenance plan that backs up availability databases.

If a database is backed up using a maintenance plan, and is then defined in the primary replica of an availability group, the maintenance plan may cease to backup the database or log of that database.

The maintenance plan will still complete successfully, but the database or log backup will not execute and no backup file will be created. If you were to right-click the maintenance plan job in SQL Server Management Studio's Object Explorer, and view the job history, it would read something like this (Note, the 'Message' does not include the BACKUP command, because it was not executed):

  

Maintenance Plan Checks Availability Group AUTOMATED_BACKUP_PREFERENCE 

When you create an availability group, the availability group's AUTOMATED_BACKUP_PREFERENCE is set. If not specifically configured, the default, through T-SQL or using the New Availability Group wizard, is 'SECONDARY.'

The SQL Server maintenance plan feature is AlwaysOn availability group aware. In SQL Server Management Studio's Object Explorer, right click your maintenance plan in and click 'Modify' - in the pane that appears to the right, right-click the Database Backup task and choose Edit. Note the new setting 'For availability databases, ignore Replica Priority for Backup and Backup on Primary Settings.'

When you define a new maintenance plan, the '...Ignore Replica Priority for Backup' setting is off by default. Therefore, the maintenance plan WILL detect the availability group's AUTOMATED_BACKUP_PREFERENCE setting when deciding to backup a database or log if that database is defined in the group.

'Given that an availability group's default AUTOMATED_BACKUP_PREFERENCE setting is 'SECONDARY, a maintenance plan, defined and running on a SQL Server instance hosting the primary replica, will NOT backup databases defined in the availability group.

To ensure that a maintenance plan backs up the database or log of an avilability database, enable the 'For availability databases, ignore Replica Priority for Backup and Backup on Primary Settings' check box and run your maintenance job. Checking the job history, you should now see 'Message' that indicates that the job is now executing the BACKUP command. Also, use File Explorer to verify that the backup file was created.

  

 Configure Your Maintenance Plan To Execute Against the Primary or Secondary Replica

When backing up availability databases, the SQL Server maintenance plan can be designed to perform availability database backup on a replica in a particular role. For example, you prefer to have backups only execute on a SQL Server instance hosting the secondary replica.

To deploy maintenance plans to always backup on the replica in a particular role:

   1 Define the same maintenance plan on all replicas you wish to backup on and which could be in the role you prefer backups to occur on, following a failover event.

   2 Configure your availability group's AUTOMATED_BACKUP_PREFERENCE to indicate whether backups should occur for databases when the databases are in the primary role or the secondary role.

   3 Configure the maintenance plan on each replica to check the availability group AUTOMATED_BACKUP_PREFERENCE, when performing a backup, by clearing the maintenance plan setting ''For availability databases, ignore Replica Priority for Backup and Backup on Primary Settings.'

When you configure your maintenance plan this way, the following job is created which detects backup preference for the availability databases using the sys.fn_hadr_backup_is_preferred_replica function:

 As it states in under the section titled 'Scripting of Backup Jobs' in TechNet topic 'Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)'

If you use the Maintenance Plan Wizard to create a given backup job, the job will automatically include the scripting logic that calls and checks the sys.fn_hadr_backup_is_preferred_replica function. However, the backup job will not return the “This is not the preferred replica…” message. Be sure to create the job(s) for each availability database on every server instance that hosts an availability replica for the availability group.

For more information see this link:

http://technet.microsoft.com/en-us/library/hh245119.aspx

For more information on the availability group's AUTOMATED_BACKUP_PREFERENCE setting, see this link:

http://technet.microsoft.com/en-us/library/hh710053.aspx#TsqlProcedure

 

 


Comments (6)

  1. D. Bachen says:

    the backup, but the backup continues to fall on the secondary replica. As far as I can tell, this is because the the backup being taken is a full backup, which is not supported on the secondary replica! The error message takes you directly to the article saying that.. Only a copy-only backup is supported. So that means that the code created by the wizard wont run out of the box.

    Seems really problematic and something I've been chasing for two days.

  2. Michael says:

    "Only a copy-only backup is supported. So that means that the code created by the wizard wont run out of the box." - yes, and this surprised me greatly...

  3. R. Fritts says:

    I believe there is possibly an opportunity for improvement in the backup task that it a part of the canned SQL Server maintenance plan.  We had a lot of issues with the backups failing on the secondary, where this article says that the backups should still succeed because they will be checked to determine the backup preference and backup the database based on the results of that function (the sys.fn_hadr_backup_is_preferred_replica function).  Like the article says, "When you define a new maintenance plan, the '...Ignore Replica Priority for Backup' setting is off by default. Therefore, the maintenance plan WILL detect the availability group's AUTOMATED_BACKUP_PREFERENCE setting when deciding to backup a database or log if that database is defined in the group."  The article indicates that you should receive a 'Success' on the job even if the database doesn't get backed up, because the maintenance plan is doing its job to check to see if it should back up the database.  If the '...Ignore Replica Priority for Backup' setting is off in the maintenance plan and the AG backup preference is set to not prefer the databases on that server, then it is correct in giving the message 'Success' even though it is not backing anything up.  HOWEVER, we still encountered issues with the job failing.  It seemed to be due to the way the code was written for the canned backup task.  In the canned backup task, the "If" statement (you can see the code by clicking the view T-SQL button on the canned backup task) does not encompass the "verify backup integrity" check-box, which causes errors and the job to fail if this check-box is checked on the maintenance plan for the databases that aren't being backed up.  The canned backup task should have included all parts when it considers the '...Ignore Replica Priority for Backup' setting IMO.  If it had done so, the errors would not have generated, and our backup plan for our secondaries would have been identical to the primary and would have not needed alterations to our maintenance plan in the event that we need to fail-over to the secondary and run maintenance off of it.  BTW, we are setting our AG to prefer primary, as this is the only way to get full backups, not copy only backups.

  4. Madhu Kiran says:

    Thanks for the ariticle, what about Index rebuild and Update Stats maintenance plans.

  5. Kevin says:

    Hey http://gkitsystems.com provides unlimited support for server and application

    issues with 24/7 professional administrators to manage and migrate your servers at just $75 per server monthly.

    They are amazing in solving technical issue of servers on both Windows/Linux.

  6. Terence says:

    I met the same problem. but if I set "Readable Secondary" to yes, then maintenance plan can backup all databases in AG. Just for your reference 🙂

Skip to main content