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

Backups \ Maint Jobs AlwaysOn SQL 2012 Expand / Collapse
Author
Message
Posted Friday, August 16, 2013 10:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:03 PM
Points: 299, Visits: 593
Hello all,

I have installed and completed a AlwaysOn 2 node VM failover cluster, what fun ! I was starting to setup the backup jobs and ran across how AlwaysOn works with this. A little back ground, we slammed this in to meet a deadline and boot contractors out so my learning was put on hold until after we went live, Ya me as the DBA!

So I went and change the back preference to Any Replica, this allowed me to back up the primary node and place the tlog BU, re-index, checkdb, update stats, clean files jobs. Now the secondary replica I want to backup as well. The notes I had read from a MVP DBA had said that if I used Any Replica I could backup both...

No Dice! I can put a copy only full backup on the secondary but it will not backup the user database, just master and MSDB.

I also found the Exclude Replica option but cannot get a clear explanation on it, it could be the lack of sleep I have had over the past week slamming this into Prod, which I am not happy about but thus is the life of a DBA, right! I am sure we all know!

Besides the backup, I want to setup Maint. Jobs on both. Now I know that AlwaysOn is Mirroring and FCI, it works, great, I know if I rebuild some indexes it will copy over to the secondary, that's great too however this is two separate databases so I want them both the be clean. :)

So my questions


a. How can I backup both P and S?
b. What Maint. Jobs should be set up on each?

Thanks!


MCSA SQL Server 2012
Post #1485282
Posted Friday, August 16, 2013 2:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 10:12 AM
Points: 110, Visits: 501
[b]So my questions


a. How can I backup both P and S?
b. What Maint. Jobs should be set up on each?

Thanks!


Hi

I think we cant make backups on both the primary and secondary.

The default set is back on secondary, you can shift it to primary if u need.

But i suppose you cant make backup on both..


You can schedule maintain jobs on both the servers, they wouldnot report fail even they dont show up bkp files.





correct me if i am wrong


Post #1485373
Posted Monday, August 19, 2013 7:22 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:03 PM
Points: 299, Visits: 593
Well, I went ot BAM and looked at a SQL book, order one online for AwaysOn as well, looks as if you can do log shipping on the secondary...looking into this option today, will report back later this week on the setup!

Thanks,


MCSA SQL Server 2012
Post #1485798
Posted Monday, August 19, 2013 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 10:12 AM
Points: 110, Visits: 501
It's the Database!!! (8/19/2013)
Well, I went ot BAM and looked at a SQL book, order one online for AwaysOn as well, looks as if you can do log shipping on the secondary...looking into this option today, will report back later this week on the setup!

Thanks,
.

I thinhk we can enable logshipping or DB mirroring or replication, But having a cluster with always on option in HA, my organization use this kind of backups

Just information

Post #1485807
Posted Thursday, September 5, 2013 7:42 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:03 PM
Points: 299, Visits: 593
Found my answer, I shall share.....

MS does not care where you backup your database however with AlwaysOn you can only backup one. The Primary or Secondary, your choice. Here’s the catch, you choose which one based on whether you want to offload resources. Let’s say that you have a 24X7 database, it would make sense to place it on the secondary. If is not a 24X7 database then you can use the golden DBA rule and run the backup at night on the primary.

Maint. Jobs -- only on the primary. You set up jobs on the secondary in case of a failover. They will run and say success even though they are not doing anything at all.

Below is a link to the article on Backups.

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


MCSA SQL Server 2012
Post #1491791
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse