Sql-server – Backup Plan on Always On using Maintenance Wizard doing Tlogs on Primary and Secondary

availability-groupsbackupsql serversql-server-2012transaction-log

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

Set up the same maintenance plan on all replicas in the AG through the maintenance wizard.
Configure the Backup Preference plan is set to Primary on the availability group.
Enable the 'For availability databases, ignore Replica Priority for Backup and Backup on Primary Settings' check box and run your maintenance job.
Now when I ran the Tlog backup on the secondary it generated a Tlog back up which was not supposed to …

Is this normal?

Best Answer

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.

Related Solutions

SQL Server – AlwaysOn Availability Groups Backup Schedules

My question is: - Do i have to schedule the jobs at primary replica and then disable them until fail-over occurs and then manually enable jobs or what is the best way to deal with these please?

No, you should just add a check with the new function sys.fn_hadr_backup_is_preferred_replica

if sys.fn_hadr_backup_is_preferred_replica('your db name') <> 1
begin
    print 'This is not preferred backup replica .....'
        -- If this is not the preferred replica, exit (without error).
end
else
begin
    print 'This is the preferred replica .. continue with backup'
        -- If this is the preferred replica, continue to do the backup.
        -- here goes the backup command .....
end

Alternatively, you can use SQL Server Maintenance Solution - by Ola hallengren which supports AlwaysON as well.

Sql-server – Text file report is not generating of Availability Databases other Databases during auto backup through maintenance plans in MS SQL Server 2012

Maintenance plans are garbage so this is running exactly as designed. However you don't need to take copy only backups on the secondary, you can if you want to but there's little point.

Any errors here are spurious. If you don't like them then your options are to switch to something professional like Hallengren scripts or Minion Backup; or ignore the errors (while maintaining normal testing of your backups to ensure they're viable).

Best Answer

Related Solutions

SQL Server – AlwaysOn Availability Groups Backup Schedules

Sql-server – Text file report is not generating of Availability Databases other Databases during auto backup through maintenance plans in MS SQL Server 2012

Related Question