Hcfyk

Unknowingly ran an infinite loop in terminal

Independent, post-Brexit Scotland - would there be a hard border with England?

Why is C# in the D Major Scale?

Is there a legal ground for stripping the UK of its UN Veto if Scotland and/or N.Ireland split from the UK?

Can Ghost kill White Walkers or Wights?

Selecting a secure PIN for building access

Why is B♯ higher than C♭ in 31-ET?

In Avengers 1, why does Thanos need Loki?

Pawn Promotion Double Checks

Upside-Down Pyramid Addition...REVERSED!

Would glacier 'trees' be plausible?

Automatically use long arrows in display mode

Catholic vs Protestant Support for Nazism in Germany

What to use instead of cling film to wrap pastry

Has a commercial or military jet bi-plane ever been manufactured?

What are the differences between credential stuffing and password spraying?

Is this homebrew life-stealing melee cantrip unbalanced?

Is Cola "probably the best-known" Latin word in the world? If not, which might it be?

Why do the derivations of ln(a/b) and ln(a) - ln(b) yield different results

What word means "to make something obsolete"?

My ID is expired, can I fly to the Bahamas with my passport?

On which topic did Indiana Jones write his doctoral thesis?

I caught several of my students plagiarizing. Could it be my fault as a teacher?

Did we get closer to another plane than we were supposed to, or was the pilot just protecting our delicate sensibilities?

Availability Group backup preference not changing to primary

AlwaysOn Availability Group Automatic Failover does not workReplicate a database to one secondary replica, but not the other secondary replica using Availability GroupScheduling backup jobs for an Availability GroupAny disadvantage to automatically changing FULL backup on AlwaysON Secondary to COPY_ONLY?SQL Server high availability fn_hadr_backup_is_preferred_replica returns 0 zero on secondaryOla Hallengren Index maintenance on servers with Availability GroupsOne Availability group, two primary nodesWhat impact does using backup preference on Availability Groups have?AlwaysOn Error: The target database is participating in an availability group and is currently not accessible for queriesBackup Plan on Always On using Maintenance Wizard doing Tlogs on Primary and Secondary

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

3

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.

sql-server sql-server-2012 availability-groups ola-hallengren

share|improve this question

edited Mar 29 at 14:32

Josh Darnell

8,64322244

asked Mar 29 at 10:32

Dennis FinkeDennis Finke

234

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  
  

  
  
  
  

add a comment | 

3

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.

sql-server sql-server-2012 availability-groups ola-hallengren

share|improve this question

edited Mar 29 at 14:32

Josh Darnell

8,64322244

asked Mar 29 at 10:32

Dennis FinkeDennis Finke

234

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  
  

  
  
  
  

add a comment | 

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.

sql-server sql-server-2012 availability-groups ola-hallengren

share|improve this question

edited Mar 29 at 14:32

Josh Darnell

8,64322244

asked Mar 29 at 10:32

Dennis FinkeDennis Finke

234

share|improve this question

edited Mar 29 at 14:32

Josh Darnell

8,64322244

asked Mar 29 at 10:32

Dennis FinkeDennis Finke

234

share|improve this question

edited Mar 29 at 14:32

Josh Darnell

8,64322244

asked Mar 29 at 10:32

Dennis FinkeDennis Finke

234

edited Mar 29 at 14:32

Josh Darnell

8,64322244

edited Mar 29 at 14:32

Josh Darnell

8,64322244

asked Mar 29 at 10:32

Dennis FinkeDennis Finke

234

asked Mar 29 at 10:32

Dennis FinkeDennis Finke

234

1 Answer
1

active

oldest

votes

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.

share|improve this answer

edited Mar 29 at 14:43

answered Mar 29 at 13:53

Josh DarnellJosh Darnell

8,64322244

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233484%2favailability-group-backup-preference-not-changing-to-primary%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

edited Mar 29 at 14:43

answered Mar 29 at 13:53

Josh DarnellJosh Darnell

8,64322244

add a comment | 

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.

share|improve this answer

edited Mar 29 at 14:43

answered Mar 29 at 13:53

Josh DarnellJosh Darnell

8,64322244

add a comment | 

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.

share|improve this answer

edited Mar 29 at 14:43

answered Mar 29 at 13:53

Josh DarnellJosh Darnell

8,64322244

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; 

share|improve this answer

edited Mar 29 at 14:43

answered Mar 29 at 13:53

Josh DarnellJosh Darnell

8,64322244

answered Mar 29 at 13:53

Josh DarnellJosh Darnell

8,64322244

answered Mar 29 at 13:53

Josh DarnellJosh Darnell

8,64322244