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
.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
Prefer secondary means that backups should run on secondary, plain and simple but they aren't running on either anymore. Any ideas?