Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup Setup with AlwaysOn Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 2:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 3:39 PM
Points: 15, Visits: 58
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
Post #1452311
Posted Monday, May 13, 2013 2:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 1,157, Visits: 3,268
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

Post #1452312
Posted Monday, May 13, 2013 3:37 PM This worked for the OP Answer marked as solution
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 3:39 PM
Points: 15, Visits: 58
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.
Post #1452343
Posted Monday, February 10, 2014 1:11 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:06 AM
Points: 7, Visits: 374
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)
Post #1539922
Posted Monday, February 10, 2014 4:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 3:39 PM
Points: 15, Visits: 58
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.
Post #1539985
Posted Tuesday, February 11, 2014 8:13 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:06 AM
Points: 7, Visits: 374
Thanks for replying.

In my case, even though I am SA for this SQL instance, that "View T-SQL" button is always greyed out ???
Post #1540223
Posted Tuesday, February 11, 2014 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 3:39 PM
Points: 15, Visits: 58
FYI, I'm on SQL 2012. Try making a change in one of the fields and see if the View TSQL button becomes available.
Post #1540309
Posted Tuesday, February 11, 2014 10:13 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:06 AM
Points: 7, Visits: 374
Thanks again Grasshopper.

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

#:7)
Post #1540312
Posted Tuesday, February 11, 2014 10:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 3:39 PM
Points: 15, Visits: 58
Sorry about that. Post it when you figure it out.
Post #1540319
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse