2

I have a new SQL Server 2012 AlwaysOn Availability Group on Windows Server 2008 R2 Ent SP1.

I'm creating a Maintenance Plan on the Primary Replica which is supposed to do Full (non-copy-only) backups of the only User database.

The task is succeeding, the subplan is succeeding, but there is no backup file when it's done.

I get the same results when I delete the Maintenance Plan and re-create it from scratch. 'FailPackageOnFailure' and 'FailParentOnFailure' are both set to True on the Subplan and the task. I have also tried enabling 'ignore Replica Priority' in the task, which didn't help.

All log entries (SQL Server Log, SQL Server Agent Log, Windows Application Log) only show generic success messages; and I'm not sure where I'd go to get more verbose information. There are other backup jobs, both in this Maintenane Plan and in others, which work fine.

What am I missing here?

Thanks in advance!

Sheree

2
  • These are brand new servers that I just built (which rules out left over or forgotten plans and scripts). The target drive is empty (which rules out free space issues), and other subplans within the same maintenance plan that back stuff up to the same destination work (ruling out a filesystem permissions issue). It doesn't look like it's staging it in a temporary location, though the database is small (100MB). I haven't used Process Monitor on it to verify that, but I wouldn't think that'd be necessary, given that the other backup jobs seem to be working fine.
    – Sheree
    Sep 20, 2012 at 16:53
  • HopelessNoob: I don't deserve your condemnation or contempt. If I did something to offend you, I apologize.
    – Sheree
    Sep 20, 2012 at 17:09

2 Answers 2

2

Turns out this is an AlwaysOn Availability Groups thing.

  1. The only replica that is capable of Full, non-copy-only backups is the primary replica.
  2. The default backup preference setting is 'Prefer Secondary'.
  3. The 'backup' portion of the Maintenance Plan's T-SQL is encapsulated in an IF block which checks the 'preferred replica' flag.

So, it looks like I have 3 options:

  1. Set the primary replica as 'preferred' (and be forced to do the log backups on the primary as well);
  2. Use T-SQL instead of a 'Backup Database' task (and then I'm the only person on my team that can fix it); or
  3. Decide that copy-only fulls are good enough, because those can be done on the secondary.

None of these scenarios is optimal, and I think it's a little screwy that normal fulls can't be done on a secondary replica in the first place. I hope Microsoft irons this out in the near future.

1
  • Great find. I'll keep this in mind when we implement AlwaysOn!
    – pauska
    Sep 21, 2012 at 0:44
1

For people with similar problem, take a look at this msdn article. It explains backup maintenance plans and SQL AG:

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

Basic idea of the article is:

  1. Setup the same maintenance plan on all replicas in the AG. If you are using wizard on the connection to an AG, then it will setup maintenance plan only on primary.
  2. Configure the Backup Preference plan 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.

P.S. Only copy-only backups are supported in AG.

1
  • Welcome to Serverfault! We don't recommend link-only answers, as the links may die. At least add a citation or abstract of the solution. Aug 12, 2017 at 7:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .