Backups \ Maint Jobs AlwaysOn SQL 2012

  • 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. 🙂

    So my questions

    a. How can I backup both P and S?

    b. What Maint. Jobs should be set up on each?

    Thanks!

    MCSE SQL Server 2012\2014\2016

  • 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

  • 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,

    MCSE SQL Server 2012\2014\2016

  • 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:

  • 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

    MCSE SQL Server 2012\2014\2016

Viewing 5 posts - 1 through 4 (of 4 total)

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