共用方式為

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:

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

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

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

Comments

  • Anonymous
    January 14, 2014
    The comment has been removed
  • Anonymous
    September 01, 2014
    The comment has been removed
  • Anonymous
    November 19, 2014
    The comment has been removed
  • Anonymous
    April 01, 2015
    Thanks for the ariticle, what about Index rebuild and Update Stats maintenance plans.
  • Anonymous
    April 21, 2015
    The comment has been removed
  • Anonymous
    October 24, 2016
    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 :)