Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
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 '12 at 16:53
    
HopelessNoob: I don't deserve your condemnation or contempt. If I did something to offend you, I apologize. –  Sheree Sep 20 '12 at 17:09

1 Answer 1

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.

share|improve this answer
    
Great find. I'll keep this in mind when we implement AlwaysOn! –  pauska Sep 21 '12 at 0:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.