Backup Setup with AlwaysOn

Question

Backup Setup with AlwaysOn

Tim-690839

Ten Centuries

Points: 1012
More actions
May 13, 2013 at 2:12 pm

#277524

I'm setting up my first pair of Sql 2012 servers using AlwaysOn. I set up backups to run on the primary and I understand that you can set up backups to run on both the primary and secondary servers but the secondary will fail. Is there a way I can stop the secondary server from sending out error messages about failed backups? Is it possible to script it so that the server looks at whether it's primary or secondary and turns on or off alerts based on that?
Thanks for any help in advance.
-tim

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 1

TimC-690839 (5/13/2013)
I'm setting up my first pair of Sql 2012 servers using AlwaysOn. I set up backups to run on the primary and I understand that you can set up backups to run on both the primary and secondary servers but the secondary will fail. Is there a way I can stop the secondary server from sending out error messages about failed backups? Is it possible to script it so that the server looks at whether it's primary or secondary and turns on or off alerts based on that?
Thanks for any help in advance.
-tim

Take a look at -

http://msdn.microsoft.com/en-us/library/hh245119.aspx

You can use sys.fn_hadr_backup_is_preferred_replica; i.e.

If sys.fn_hadr_backup_is_preferred_replica( @dbname ) <> 1

BEGIN

-- If this is not the preferred replica, exit (probably without error).

END

-- If this is the preferred replica, continue to do the backup.

Tommy

Follow @sqlscribe

Tim-690839 Ten Centuries Points: 1012 More actions · Answer 2

Thanks.

I used the Maintenance Plans wizard to create the backup jobs and I see in there that it contains the following for each db:

DECLARE @preferredReplica int

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

IF (@preferredReplica = 1)

BEGIN

I found that the backup job was failing and sending out errors on the secondary server if I had "Verify backup integrity" checked.

bbcolo22 SSC-Addicted Points: 465 More actions · Answer 3

How do I see the SQL that is being run by my Maintence Plan Subplan? I am able to view it via Profiler, but there must be an easier way.

(Thanks)

Tim-690839 Ten Centuries Points: 1012 More actions · Answer 4

In Management Studio, under Management -> Maintenance Plans, open your maintenance plan. Double click or Edit the task that you want to view. At the bottom of the box that pops up is a "View T-SQL" button. Hit that and it will show you what's running behind the scenes.

bbcolo22 SSC-Addicted Points: 465 More actions · Answer 5

Thanks for replying.

In my case, even though I am SA for this SQL instance, that "View T-SQL" button is always greyed out ???

Tim-690839 Ten Centuries Points: 1012 More actions · Answer 6

FYI, I'm on SQL 2012. Try making a change in one of the fields and see if the View TSQL button becomes available.

bbcolo22 SSC-Addicted Points: 465 More actions · Answer 7

Thanks again Grasshopper.

It's trivial, but for failures View-TSQL is clickable, and for successes it is not.

#:7)

Tim-690839 Ten Centuries Points: 1012 More actions · Answer 8

Tim-690839

Ten Centuries

Points: 1012

February 11, 2014 at 10:23 am

#1688004

Sorry about that. Post it when you figure it out.