SQL Server 2014 with most DB in AG. Backup preference set to prefer Secondary.
- Plan 1 backup all user DBs (AG and non AG)
- Plan 2 backup all user DB trans logs
Both .bak
and .trn
files were created with no issues. Received error email from the one that did not finish job due to AG settings. But .bak
files were created by one or the other instance.
These setting WERE WORKING fine up until 2 months ago.
Now for some reason, no .bak
files at all for ANY AG database are created.
BAK: bak files for nonAG DBS are created by both primary and secondary. NEITHER the Pri or Sec are creating bak files for any AG Db.
TRN: The trn are created fine on secondary for all AG and nonAG DBs. The primary only has trn backups for nonAG. SO TRN backups are working the way they should.
So what changed?
I know that there were VM upgrades done during this time and I also installed the latest SQL SP.
The whole point of selecting prefer Secondary option is so backups happen on Sec, both bak and trn. Selecting ignore priority option in plan setting is NOT a solution it is a band-aid.
The only thing close to what is happening I found here https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/02/maintenance-plan-does-not-backup-database-or-log-of-database-defined-in-availability-group/ but that is also vague as it says
the maintenance plan may cease to backup the database or log of that database
Seriously: may.
Prefer secondary means that backups should run on secondary, plain and simple but they aren't running on either anymore. Any ideas?
A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups
. It would work fine as a sec backup source. I have changed the AG settings from sec backup to AG primary only and all seems to be working fine. Senior DBA was adamant on having bkups on Sec and wanted a fix, without copy-only or ignore... total reversal after MS ticket and realizing there isn't really one. – CrashWave Jul 28 '17 at 15:37