September 20, 2012 at 10:35 am
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!
September 20, 2012 at 6:37 pm
Turns out this is an AlwaysOn Availability Groups thing.
- The only replica that is capable of Full, non-copy-only backups is the primary replica.
- The default backup preference setting is 'Prefer Secondary'.
- 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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply