SQL AG Backup Strategy

Has anyone used RR to backup a SQL AG and if so what was the strategy?

We have several large SQL DB's (10TB) that run on three node AG's and are looking for a good solution for backup and recovery but nothing seems great and most all require human DBA intervention (dump and sweep). I have used RR on single node small SQL DB's and it worked well but nothing like this scale.

These are production DB's with high transaction rates.

Parents
  • Question, sorry if this is off base too, are you asking for the best way to back it up? Or the best way to go about restoring it? For the backup (depending on version) the cluster option in RR should be able to identify the active node and pick it up (unless there is a complexity internally that would prevent that). The log truncation would work in the same manner, it'd pick up the active DB imho. You have me intrigued now as to what you're looking for, happy to have that dialog if you like as yes, now I'm curious what you're looking for. Cheers. 

  • Both actually.

    All the solutions I am finding now, including Veeam, suggest you need a DBA to manage the logs. Everything we do now involves DBA's actually doing all the backups for SQL via dump and sweep which we then write off to tape.

    When I used it for small single node SQL I could work it a few ways.

    I could live recover the whole data drive
    I could take the DB offline and manually replace all the MDF and LDF's
    I could fire up my standby VM copy of the machine or it's offsite replicated copy.

    But it was single two volume machine (1 OS and 1 data drive) with under a TB total.

    These AG nodes have 10-12 volumes each and are all part of the an AG. Does it work the same? Can I just live recover all the volumes at once? Lets assume a crypto situation where everything is lost.

Reply
  • Both actually.

    All the solutions I am finding now, including Veeam, suggest you need a DBA to manage the logs. Everything we do now involves DBA's actually doing all the backups for SQL via dump and sweep which we then write off to tape.

    When I used it for small single node SQL I could work it a few ways.

    I could live recover the whole data drive
    I could take the DB offline and manually replace all the MDF and LDF's
    I could fire up my standby VM copy of the machine or it's offsite replicated copy.

    But it was single two volume machine (1 OS and 1 data drive) with under a TB total.

    These AG nodes have 10-12 volumes each and are all part of the an AG. Does it work the same? Can I just live recover all the volumes at once? Lets assume a crypto situation where everything is lost.

Children
  • I saw your other post out here and I now see a reply to this one from before (sorry, I missing this completely somehow) whoops. 

    I do agree with your examples: 

    I could live recover the whole data drive.  Yes
    I could take the DB offline and manually replace all the MDF and LDF's.   Yes
    I could fire up my standby VM copy of the machine or it's offsite replicated copy.   Yes

    These AG nodes have 10-12 volumes each and are all part of the an AG. Does it work the same? Can I just live recover all the volumes at once? Lets assume a crypto situation where everything is lost.   Yes

    Now with the more volumes/data it will take that much longer indeed to do the restore/recovery, it does work the same though. 

    For the other piece: 

    "All the solutions I am finding now, including Veeam, suggest you need a DBA to manage the logs. Everything we do now involves DBA's actually doing all the backups for SQL via dump and sweep which we then write off to tape."

    All I can say is 'personal opinion' here, everyone has a disclaimer that tries to get you to work with an SQL centric product, utility, or yes the default 'ask your DBA.' it just does seem that way as yes I've had various conversations with vendors myself. With that being said though, the old tried and true restore methods of restoring the volume or the MDFs/LDFs does just in the end work. There might be a 'better' way, or a more 'streamline' way with an SQL centric product, however that doesn't change the fact that the 'tradition' restore methods work just time. 

    I'm sure this is where Quest would like to sell you LiteSpeed or something like that. However even with those, it isn't that the backup/restore from the volume/block level backups don't work, that's just gets you the granular ability of individual tables/entries/queries and such.