4

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?

ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306
CrashWave
  • 107
  • 1
  • 1
  • 8

1 Answers1

1

Our Senior DBA ended up opening a Microsoft ticket for this and so far the only solution that does not involve selecting ignore priority or copy-only is to change the Availability Groups backup settings to backup on Primary instead of Secondary. We have MANY databases and many of them are quite large and accessed throughout the day; we did not initially want to run on Primary due to the server load this could create. So for anyone who is going through a similar situation, here are the tests I did and why we came to this conclusion. Again choosing Primary Backup for Availability Group always-on settings is not a fix to why Secondary option does not work the way it should, but it is the best solution if you want latest full database backup.

  • Prefer Secondary for AG backups
  • Created 2 plans one for DB backups (bak) and one for Trans Log (trn)

Note: When I first started testing I had one plan with both DB backup (bak) and transaction log backup (trn) but would fail and stop when bak were not created on secondary and no trn created and wanted to test each. separated bak and trn

  • Added both AG and nonAG Dbs to the plans

1. Test 1

NO options selected

Trans Log plans works as it should, Primary only had trn for nonAG DBs and Sec had trn for all Dbs, no errors on either

Bak files were created on both Pri and Sec but only for nonAG DBs

No errors from primary, not expecting there to be on primary as it is not set as AG backup.

Sec is the only one that triggered an error...

"This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally."

2. Test 2

Selected ignore replica on DB backups only.

Bak files were created on PRIMARY

NO bak files on Secondary

Secondary triggered same error above

3. Test 3

Unchecked ignore Priority and checked copy-only

Secondary created both AG and nonAG bak files

Primary only had nonAG bak files

4. Test 4 (NOT AN OPTION AT ALL)

Both Ignore and copy-only checked

ALL DB backed up on both Pri and Sec

CrashWave
  • 107
  • 1
  • 1
  • 8