4

I have an availability group setup and I'm using Ola Hallengren backup scripts. At setup of the Availability group, the default option of Prefer Secondary was set, where it should have been changed to primary.

Once I noticed this, I tried to set the backup preference to Primary, but it doesn't change, it stays on Prefer Secondary.

Has anyone come across this before? I'm using the user interface (availability group wizard) to make the change.

  • Do you also have the "backup priority" setting configured? I've run into problems with that in our AAG. I.E. Set to "no preference", but primary still doesn't take a backup because secondary "priority" is set to "50" and primary was set to "48". – Jacob H Mar 29 at 13:23
  • 1
    @JacobH thanks, one of my secondaries did have a different Priority due to it being on my DR site, so I lowered it but in the end i opted for the T-SQL option that chnaged it for me. ALTER AVAILABILITY GROUP [<AG NAME>] SET (AUTOMATED_BACKUP_PREFERENCE = PRIMARY) – Dennis Finke Mar 29 at 13:41
  • Dennis, so we are clear, there are two settings that we're referring to, AUTOMATED_BACKUP_PREFERENCE and BACKUP_PRIORITY. Both are required to be configured for Ola's scripts to function. This is what I've found. – Jacob H Mar 29 at 14:36
  • 1
    There seems to be a bug in SSMS (17.9.1 is what we're using here) where any change to the backup preference does not get saved, and it is not possible to generate a script from the GUI either for that change. The TSQL command given by Dennis above is the only way I was able to make the change stick - using the GUI did not do it. – Mike May 8 at 1:26
2

I tried to set the backup preference to Primary but it doesn't change, it stays on Prefer Secondary

I'm not sure if you mean the user interface, or if the actual effect of the change didn't occur as you expected. My answer assumes the latter.

To be clear about something that I find a little confusing: this setting is not something that automatically affects how backups are run. It's simply a place where you, as a DBA, can store "metadata" about how you prefer backups to be taken.

This is documented, but it's not obvious at all from the user interface. Here's the quote from the docs:

❕ Important

There is no enforcement of the AUTOMATED_BACKUP_PREFERENCE setting. The interpretation of this preference depends on the logic, if any, that you script into back jobs for the databases in a given availability group. The automated backup preference setting has no impact on ad hoc backups.

After setting this, you need to update your backup jobs with code like this (also lifted from the docs):

IF (NOT sys.fn_hadr_backup_is_preferred_replica(@DBNAME))  
BEGIN  
      Select 'This is not the preferred replica, exiting with success';  
      RETURN 0 - This is a normal, expected condition, so the script returns success  
END  
BACKUP DATABASE @DBNAME TO DISK=<disk>  
   WITH COPY_ONLY;  

Using sp_helptext to view the source of sys.fn_hadr_backup_is_preferred_replica, it can be seen that this takes into account both the AG-level AUTOMATED_BACKUP_PREFERENCE setting, and the replica-level BACKUP_PRIORITY setting.

Note: since you're using Ola's scripts, this is already taken into account


If you just didn't see the value change in the user interface, I would suggest using the "script" button to show the T-SQL that's being run by the UI, and then running that script manually to see if the change is properly made.

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.