Mirrored Backups

  • Andy Warren

    SSC Guru

    Points: 119676

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Andy,

    Great article!  I especially like the fact you pointed out what happens when the backup fails on part of the mirror, and I'd echo your sentiments... be good if there was a switch to continue the process.

    I currently use a disk to disk to DVD solution at my client's location, it works for them, but the amount data isn't too terribly large.

    Mark

  • Andy Warren

    SSC Guru

    Points: 119676

    Backing up to DVD means you can sleep well! Not often you can do that (back up to DVD that is) and good to use the option if it fits.

  • Malcolm Matthews

    SSC Veteran

    Points: 202

    Good article, great feature with a fatal flaw!

    We've been using DeltaCopy http://www.aboutmyip.com/AboutMyXApp/DeltaCopy.jsp to mirror backups.  It's particularly good for WAN backups if not much has changed as it uses very little bandwidth.  It can be easily scheduled and will send email reports.

    Malcolm

  • JG-219322

    SSC Veteran

    Points: 207

    Thanks for the great article!   Another item I would like to see is doing the mirror for both disk and tape not disk/disk and tape/tape.

     

    JG

  • John Pertell

    SSC Rookie

    Points: 35

    Great article. Unfortunately mirrored backups aren't available in the Standard Edition, which is what my client is using.

  • Andy Warren

    SSC Guru

    Points: 119676

    The backup util is interesting, havent looked into that area much, might have to give it a try!

  • Andy Warren

    SSC Guru

    Points: 119676

    I didnt catch the standard edition limitation, did my testing on Dev edition which of course has the Enterprise features. Dont think I'd upgrade to get this particular feature. I know they need to save a few features to make the extra dollars worth spending, but often it seems like they are forcing you to upgrade for convenience rather than super features.

  • Jason1972

    SSC Veteran

    Points: 206

    Nice bit of knowledge. My issue would be that there are other factors that could affect the performance of the backup - especially network latency.  We should keep this in mind when using database backup mirroring.  Also, the database size was relatively small in the sample shown.  I'd like to see performance of a larger database backup, and to see what the network latency would then be for other applications going over the same LAN during tha mirrored backup process.

  • Daniel Wolford

    SSC-Addicted

    Points: 451

    Great article.  It's nice to see some basic new features every once in a while.

    Unfortunately, I presume that the fault of an all or nothing backup will make this too risky for most shops to use.  In ours, if it's not on tape and off-site, it's not a successful backup.  Even if I have the local copy. 

    I've stuck with the traditional copy/ftp method, but use SQL Backup from Red Gate to accomplish it.  They have gone ahead and incorporated the CopyTo command inline with the backup command, making it very easy to set up a job to backup to several locations consecutively. 

    I just checked their new version 5 help file, and apparently they have also included a MirrorFile option that allows up to 32 mirror locations.  However, according to the documentation, they will continue with the backup even if only one of the locations specified works. 

    Add compression, encryption, and faster backup and restore speed, and the native Backup for SQL Server just doesn't have a chance to be used by me.

    You can go here to review the help file online, if curious: (No, I don't have stock in them.  For some reason, I get accused of that a lot.  Besides, I'm sure the new site owners won't mind.)

    http://help.red-gate.com/help/SQLBackup5/0/en/SQLBackup.htm

    Keep up the good work, and I hopefully I'll see you in your classroom sometime soon.

    Dan

  • Andy Warren

    SSC Guru

    Points: 119676

    I have not looked at their feature set recently, thats interesting that they have added value over and above just compression. Wonder if the competitors offer similar features?

  • Wayne West

    SSC-Insane

    Points: 22586

    Andy, I'm curious if there is a typo in your article:

    "This gives you the ability to backup to two or more locations simultaneously (which is the same thing as a striped backup, that's a different topic!)."

    Should that be "which is not the same thing"?

    Currently I'm reconfiguring my backups to first go to the server's disk, then go to our snap server. I'm also working on a system that will restore the backups to a DB and run a compare between the two. Fun stuff!

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Andy Warren

    SSC Guru

    Points: 119676

    Ouch, yes its a typo and/or poorly worded. Striped backups write a single logical backup across multiple physical locations, mirrored backups write multiple logical backups to multiple physical locations. Not sure I said that any better the second time!

  • Wayne West

    SSC-Insane

    Points: 22586

    I was fairly certain what your meaning was based on my experimentation, but your article said otherwise and I wanted to make sure.

    Thanks!

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • BobAtDBS

    SSCarpal Tunnel

    Points: 4583

    Our strategy is to backup across the network from the primary sql server to the backup sql server.  That way if I need to turn the backup into the primary the data is already there.  On a weekly basis, we copy the most recent full backup to a USB drive manually and swap it with an second drive so that one is always off site.

    Our main database is a bit over 100 gig, and the transaction log backups take less than a minute each hour.  The full backups take about 50 minutes each night.  We use a gigabit network to connect the servers.


    Student of SQL and Golf, Master of Neither

Viewing 15 posts - 1 through 15 (of 25 total)

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