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?

|improve this question

migrated from stackoverflow.com Jul 26 '17 at 15:02

This question came from our site for professional and enthusiast programmers.

  • 1
    First thing I would check is that the copy_only flag is included in the backups scheduled on that secondary replica, otherwise it won't backup those databases. It is also time to move away from maintenance plans. They are notoriously bug ridden. Check out minionware.net/backup (free) or something similar to help. – Nic Jul 26 '17 at 15:22
  • Thanks for the comment. Yes, we tried copy_only and though it creates the entire DB backup as the primary backup source 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
  • I've been running copy_only backups on AG secondary replicas for years. There's no significant detrement to doing so (careful around your TLogs). Why is your Sr DBA so adamant about not using copy_only? – Nic Jul 28 '17 at 15:50
  • the simple answer is that's the way he wants it. Not so simple link – CrashWave Jul 28 '17 at 17:06
  • 1
    Which makes sense if you want to do differential backups, but then if you wanted to do differential backups you wouldn't be taking backups off the secondary. It only reset the differential base, you can still restore a backup and apply transaction logs to get to a current state. Your Sr sounds less than. – Nic Jul 28 '17 at 17:57
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

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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