Bug in sys.fn_hadr_backup_is_preferred_replica

While testing backups in SQL Server 2014 Availability Groups I hit a bug in a system function that is used to determine which replica is the most appropriate to be backed up.

You can set the desired backup pattern by RIGHT clicking on the availability group to bring up the properties:

 

image

 

I’ve decided I want my backups to happen on my PRIMARY replica, leaving my READ ONLY reporting replica available for client reporting.

Then I setup a backup maintenance plan using the out-of-the-box Maintenance Plan builder to setup the FULL, DIFF and LOG backups.

The script used in the maintenance plan looks something like this:

[sourcecode language='sql'  padlinenumbers='true']
DECLARE @preferredReplica int

SET @preferredReplica = 
(SELECT [master].sys.fn_hadr_backup_is_preferred_replica('myDB'))

IF (@preferredReplica = 1)
BEGIN
    BACKUP LOG [myDB] TO  
	DISK = ... <The rest of the backup command>
END
[/sourcecode]

The bug was the function sys.fn_hadr_backup_is_preferred_replica always returned zero.  This resulted in SQL Agent jobs that complete successfully, but the backup never ran.

In this particular case the server was created from a VM template. The servername did not match the instance name, which caused the function to always return zero.

The fix… Rename the @@servername value for all instances in the AG.  Something like this did the trick:

[sourcecode language='sql' ]
SELECT @@servername; -- Returned CHANGETHIS
sp_dropserver 'CHANGETHIS';
sp_addserver 'HSVSQLAG02D', local;
[/sourcecode]

A quick restart of the instances and voila, the log backups actually worked.

Back to testing AG backup strategies.

Stay curious; keep learning…

0 Comments

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>

the ugly truth about beauty dave barry report editor websites uk best blog post ghostwriting site au best papers editor website for school professional admission essay proofreading site for phd professional phd essay editing sites gb cheap persuasive essay editing website us write me top phd essay on donald trump college ghostwriter for hire uk esl dissertation chapter ghostwriters service for school pay to get logic cv custom papers ghostwriting sites for phd top analysis essay writer services for college top annotated bibliography ghostwriting sites for school reflective proofreading websites online popular dissertation results ghostwriters sites uk top college cheap essay samples top application letter ghostwriting site us professional thesis proposal ghostwriters services for college eric foner voices of freedom top persuasive essay writer services au help writing u.s. history and government dissertation abstract professional research paper editing sites online custom business plan ghostwriting sites for college best research paper ghostwriter service uk popular dissertation chapter editing for hire for phd esl dissertation abstract proofreading website online top resume ghostwriter services for university custom report writers sites ca mills harm principle best essay editor services usa esl term paper editor site us advantages and disadvantages of federal government pay for my top dissertation chapter online professional cheap essay ghostwriting services au professional home work editor service for phd pay for my speech literature review popular research proposal ghostwriters for hire for mba order women and gender studies term paper personal essay ghostwriting for hire au help with best phd essay on civil war top resume writers services for mba professional cv writing site online popular creative essay on brexit type my analysis essay on civil war pay to do custom reflective essay on donald trump cheap problem solving ghostwriters for hire for university cheap argumentative essay ghostwriter services us essay on kindness esl resume editor service usa cheap dissertation chapter ghostwriting websites for college top research paper writer service for college custom writing website us top home work writer for hire us popular school essay ghostwriters sites for college custom application letter ghostwriter services ca esl personal essay proofreading services for school stereotypes of blind people pay to get nursing admission paper cheap research proposal editor for hire uk top bibliography writer site for university custom school letter sample write my classic english literature paper custom university speech advice custom dissertation methodology ghostwriting website usa best analysis essay ghostwriters for hire for school help writing cheap rhetorical analysis essay on founding fathers admission essay proofreading services online write technology critical thinking popular report ghostwriting websites for masters professional dissertation abstract editor sites best critical thinking writing services au top business plan writers services usa professional dissertation conclusion ghostwriter service top thesis statement ghostwriter website for school top school essay editing for hire usa order popular personal essay on presidential elections popular assignment writers sites au help me write top paper political science writer for hire order composition article review custom custom essay editing website online write my theater studies blog professional argumentative essay ghostwriters services for college professional university essay editor services gb popular speech writing services online esl literature review ghostwriting websites for mba personal essay writer websites ca custom research proposal ghostwriting websites for university professional scholarship essay editor sites for college professional critical essay writers websites for school pay for cheap expository essay on shakespeare best curriculum vitae writing site for masters custom dissertation abstract proofreading sites uk top admission paper ghostwriters site ca top cv writer for hire usa pay for my botany article review popular blog post proofreading sites online professional cheap essay writing site for phd esl dissertation methodology writers services gb custom critical thinking ghostwriters websites for phd mission santa ines facts best school research paper examples best masters creative essay ideas professional critical analysis essay ghostwriter site online help with calculus personal statement pay to do geometry dissertation methodology popular critical thinking writing for hire us cheap application letter editor site au pay for medicine thesis a and p by john updike forrest gump essay custom case study editor site for university do my professional rhetorical analysis essay on donald trump popular movie review editor sites online popular college essay ghostwriter website gb brighton beach memoirs characters best resume ghostwriting website for phd professional homework ghostwriting for hire for mba custom cheap essay writing site online thesis proofreading service anthropology essay writer website order women and gender studies dissertation conclusion professional speech proofreading for hire gb antigone creon tragic hero custom term paper editor site for school xenotransplantation pros and cons cheap dissertation writers site gb popular business plan proofreading site for mba best home work writers service uk letter editing services gb top book review writer site uk write me statistics business plan best course work ghostwriters website for phd cheap critical essay proofreading websites gb top application letter editor site us top rhetorical analysis essay editor sites for school free enterprise system characteristics frankenstein theme essay write my leadership letter top blog post writers for hire for university order best personal statement online best dissertation writer service for masters esl dissertation conclusion writing sites for school help me write theater studies thesis cheap argumentative essay writing services top article review proofreading website for college type my anthropology creative writing esl cover letter writers site for phd professional speech editor for hire for school professional course work proofreading services uk pay to get algebra dissertation introduction help with geology blog esl definition essay editor website uk the broken column professional research proposal proofreading site for school do my custom masters essay on shakespeare pay for my professional resume online write my speech article esl phd application letter assistance cheap school movie review help pay to write professional persuasive essay esl dissertation proposal ghostwriters websites uk book review editing website gb top mba custom essay ideas best custom essay writing site ca cheap biography writer websites ca pay to write best persuasive essay on hillary clinton top homework ghostwriters site us custom report ghostwriter for hire for university best blog writers site for school popular case study ghostwriter websites usa pay to do logic book review best cheap essay writing websites for school help writing culture research paper professional argumentative essay editor sites for school cheap annotated bibliography proofreading website ca esl argumentative essay ghostwriters site online buy best university essay on pokemon go pay for best cheap essay on hillary clinton top article ghostwriter for hire for mba esl cover letter writer site uk cheap rhetorical analysis essay editor website us top movie review ghostwriter websites uk creative essay editing sites us top masters essay editor services ca help with top rhetorical analysis essay on civil war our secret by susan griffin do my literature thesis best critical thinking editing site gb professional cv writer services gb spartas religion help writing music resume honor code essay cheap school essay ghostwriting for hire professional persuasive essay editing services online popular dissertation introduction writer service au esl book review ghostwriter services for phd the harlem renaissance langston hughes help with english thesis professional book review writers service for masters course work editing sites cheap ghostwriting website us custom dissertation proposal ghostwriter site for masters esl home work editor site for college creative writing ghostwriting service us esl expository essay proofreading services for masters write me best academic essay on brexit popular article review editor websites us cheap critical essay writers services for mba popular dissertation chapter editor services for school pay to get custom expository essay on trump type my psychology article do my cheap dissertation abstract online university editor services us cheap essay on shakespeare studies popular resume ghostwriter service ca professional dissertation abstract proofreading services custom creative essay writers sites online argumentative essay immigration top dissertation abstract editor services cheap curriculum vitae ghostwriters site cheap persuasive essay editor website us esl custom essay ghostwriters websites usa forgiving my father poem professional papers writing service for school top blog post editing service au buy cheap scholarship essay on lincoln rhetoric of the image roland barthes cheap letter editor websites usa top letter writers for hire for school custom best essay writing sites au popular argumentative essay ghostwriters sites ca professional business plan proofreading services au superstition in huckleberry finn creative essay writing services au best thesis proposal ghostwriters websites for masters best dissertation chapter editing services gb pay to write cheap phd essay on lincoln custom argumentative essay editing services ca thesis proposal writing for hire au professional creative essay ghostwriting sites au critical essay on criminal law custom case study writer site for school teenage wasteland analysis best university definition essay sample top persuasive essay proofreading sites top dissertation chapter proofreading services ca personality theory analysis paper a critique of utilitarianism bernard williams cheap thesis statement writer services for masters letter example popular academic essay ghostwriting website esl cover letter ghostwriters website for masters order sociology literature review best dissertation chapter ghostwriter service au best application letter ghostwriting site online teamwork and diversity top expository essay writer site for university custom scholarship essay ghostwriters sites gb plains indians climate cheap critical analysis essay ghostwriting website for school mercutio personality traits cheap thesis proposal proofreading sites for school esl literature review proofreading site for school cheap personal essay writer for hire us cheap dissertation chapter ghostwriter website ca write cheap definition essay on presidential elections theme of because i could not stop for death popular dissertation abstract proofreading websites for mba professional essay editor site for phd professional thesis statement writing for hire for university popular definition essay ghostwriting site for college cheap blog editing service for college top business plan proofreading services au best article ghostwriters service ca human resource management editing sites professional thesis proposal writer services for phd custom scholarship essay ghostwriter sites for university do my health dissertation introduction critical thinking editor service ca professional descriptive essay ghostwriter website us custom school dissertation example custom school biography ideas do my speech presentation efficiency vs equality popular bibliography ghostwriters sites for university custom research proposal ghostwriters website online best essay ghostwriters services online pay to get popular assignment popular college cv help nursing essay proofreading sites popular presentation editor for hire uk professional homework editing website usa best rhetorical analysis essay editor service for mba top personal statement ghostwriter websites for university custom application letter writing services for mba buy custom critical essay on founding fathers invisible man battle royal popular case study ghostwriters website for college professional dissertation proposal writer site us physics essay ghostwriter for hire best admission paper writer service for phd custom admission paper editor service gb top paper editing services cheap cover letter ghostwriters site online professional article review ghostwriters service uk custom literature review ghostwriting service usa custom school essay writing sites us order economics admission paper professional phd essay ghostwriter for hire for college esl college essays example custom personal essay writer site for school order psychology personal statement professional dissertation editing sites for university type my professional custom essay on hillary march 1917 russian revolution custom blog ghostwriting website for phd haircut ring lardner best college essay editing services for university do my cheap university essay on trump professional definition essay editor site top literature review editing site for college popular blog post proofreading site online professional definition essay proofreading websites uk popular personal statement proofreading site usa professional essays editor website uk type my cheap critical essay on presidential elections pay to do education dissertation proposal custom term paper writer for hire for school best descriptive essay writers site au pay for my professional creative essay on hillary clinton esl dissertation conclusion editor for hire ca custom analysis essay editor site us professional dissertation abstract ghostwriting for hire us professional dissertation abstract editor websites for mba order earth science curriculum vitae custom personal essay ghostwriters for hire au top blog editor service cheap cover letter editing site uk top academic essay writing service for college article writing websites usa top book review ghostwriter site for university cheap blog ghostwriters for hire for university custom papers ghostwriting services for college cheap thesis statement ghostwriting service online japan annexes korea esl literature review editor websites esl personal statement writer services au pay to write literature essays help me write popular phd essay on donald trump popular content writer service for mba popular homework editor for hire uk popular dissertation methodology ghostwriter service online pay to write environmental studies papers popular admission essay ghostwriter sites for school professional case study writer for hire for masters help with government thesis statement best report editor website for phd dissertation results writing pay to do professional research proposal cheap analysis essay writer website gb top scholarship essay editor websites au professional descriptive essay writing website usa best ghostwriters websites for college help me write psychology resume best personal statement writer sites for college pay to get drama business plan custom critical analysis essay writer website online professional reflective essay editor sites online professional dissertation writer sites usa cheap creative writing writing sites us pay for cheap university essay on civil war pay for tourism cv pay to get u.s. history and government application letter school essay proofreading sites gb professional movie review editor service for mba write me algebra dissertation introduction professional article writing for hire for university esl college dissertation chapter sample write popular critical thinking online top dissertation methodology writers websites uk help with my composition personal statement custom phd essay proofreading websites for college custom blog post proofreading website for college cheap cv proofreading for hire ca professional thesis statement ghostwriter site for school best research proposal writing service for mba cheap personal statement writer services ca popular dissertation methodology ghostwriting service gb popular thesis writer for hire for school popular paper writers site us critical ghostwriters services gb top research proposal ghostwriters services gb esl analysis essay on brexit popular cv ghostwriter site ca cheap cv writers websites for phd professional case study proofreading services for university popular best essay ghostwriters sites gb esl dissertation hypothesis ghostwriters services uk cheap argumentative essay editing sites us esl expository essay writer service for university esl definition essay ghostwriters websites for masters best expository essay ghostwriters websites for phd esl creative writing editing site online professional critical essay writing for hire online professional critical analysis essay writers services usa best dissertation chapter ghostwriters service for college custom report ghostwriters website online best cover letter proofreading websites for masters help with cheap reflective essay on lincoln cheap literature review writers for hire for university esl letter ghostwriter site ca best dissertation proposal ghostwriter service online cheap descriptive essay ghostwriters services custom course work ghostwriters services gb professional speech ghostwriters website for university custom phd essay writing sites online custom article ghostwriting site for college top assignment writer service usa professional thesis statement proofreading sites au logic ghostwriter websites custom masters essay proofreading service gb best research proposal writer website ca research paper ghostwriter sites us esl masters dissertation abstract custom admission essay proofreading for hire for university write custom definition essay on usa type my leadership thesis esl research proposal writing site for school top dissertation abstract ghostwriting website for mba best research proposal writers sites for mba top ghostwriters for hire for college cheap letter writing for hire au best persuasive essay writers sites for masters top admission paper ghostwriters for hire usa cheap essays writing service for mba custom research paper editor services gb creative essay editor service us best dissertation hypothesis ghostwriting for hire for mba esl blog post ghostwriters for hire for school professional university essay proofreading website usa dissertation results proofreading sites ca write esl critical essay pay for popular reflective essay on lincoln order best school essay on hillary clinton help writing psychology paper pay to get english literature review write my culture content esl literature review ghostwriter services uk cheap dissertation proposal ghostwriting for hire us professional critical analysis essay proofreading websites gb pay to get medicine literature review esl dissertation writing services professional dissertation hypothesis editing site for masters argumentative essay on donald trump custom dissertation hypothesis ghostwriter website online top article writers for hire au top dissertation results writers websites for college esl bibliography ghostwriting site uk dissertation results writer service usa top bibliography writing services for mba expository essay top book review proofreading service gb custom dissertation hypothesis ghostwriter websites online research proposal writing websites top creative writing writer website for phd content writing website us health editing site pay to write esl academic essay on brexit professional thesis statement editor site for school do my esl dissertation proposal online report assistance cheap personal statement writers service online school essay ghostwriter websites gb top dissertation introduction ghostwriters services for college cheap article review proofreading site for mba personal statement editor service au analysis essay proofreading services gb cheap masters essay editor sites for mba write my popular case study cheap college essay writers service au cheap critical thinking proofreading for hire uk professional masters essay writing sites us esl definition essay proofreading services uk journalism essay ghostwriting sites esl masters essay proofreading site uk help with top dissertation chapter pay to write life science problem solving cheap report writing site for mba professional admission essay writing service uk esl university essay writer service gb popular movie review proofreading services for mba cheap dissertation methodology editor websites usa popular persuasive essay writer websites usa buy top custom essay cheap college article topics why volunteer at a hospital essay popular application letter writing sites usa marrying absurd analysis