Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backups \ Maint Jobs AlwaysOn SQL 2012


Backups \ Maint Jobs AlwaysOn SQL 2012

Author
Message
lkennedy76
lkennedy76
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 757
Hello all,

I have installed and completed a AlwaysOn 2 node VM failover cluster, what fun :-D! 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. Smile

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
rajborntodare
rajborntodare
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 510
[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.



Hehe

correct me if i am wrong
lkennedy76
lkennedy76
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 757
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
rajborntodare
rajborntodare
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 510
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

Hehe
lkennedy76
lkennedy76
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 757
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search