RAID your backups

6

May 16, 2016 by Kenneth Fisher

Let’s start with a very brief definition of some RAID levels.

  • RAID 0 : Stripe your data across multiple disks. Writing a portion of the data to each disk. This improves performance but increases the risk of corruption.
  • RAID 1 : Mirror your data across multiple disks. This duplicates the data identically on each disk. This reduces the risk of corruption but decreases performance.
  • RAID 10 : Combine both methods. Stripe your data across multiple disks and mirror it at the same time.

 
Interestingly these same options are available with our SQL Server backups.

RAID 0

Splitting the backup data between multiple files.

This is actually a fairly common way to speed up a large backup. IO is one of the slowest part of the whole process so by splitting the backup into multiple files we can reduce our backup time by quite a bit. Having multiple files will also increase your restore time but you do have to make sure that all of the files are available. If you lose one file then the whole backup is useless.

This is done by listing multiple locations for the backup.

BACKUP DATABASE AdventureWorks2014
TO DISK='C:\Backups\AdventureWorks1.bak', 
   DISK='D:\Backups\AdventureWorks2.bak', 
   DISK='E:\Backups\AdventureWorks3.bak'
WITH FORMAT
RAID 1

Creating multiple copies of the backup data.

I don’t see this a whole lot but if you happen to need more than one copy of a backup then this can save you some effort. SQL will write up to 4 copies of a backup (the main copy + three mirrors). All of the copies have to all be of the same type. IE DISK, TAPE or URL. One of the benefits of this is the increased likelihood that at least one of your copies will be restorable. Another use for this clause is to create a second copy to be used for an alternate purpose (send to the vendor for example) while still keeping a copy in your normal backup location. You can of course take the backup and copy it afterwards but this lets you do it in a single command and should be quite a bit faster.

Important note: This is an Enterprise only feature.

We use the MIRROR TO clause to create extra copies.

BACKUP DATABASE AdventureWorks2014
TO DISK = 'C:\Backups\AdventureWorks_Mirror1.bak'
MIRROR TO DISK = 'D:\Backups\AdventureWorks_Mirror2.bak'
WITH FORMAT

Warning: This option should be used with caution. If the backup fails at any of the mirror locations then the entire backup fails, including the original.

RAID 10

Combining both striping and mirroring.

Last but not least you can combine the two options creating multiple striped backups. This has all the restrictions of striping and mirroring but includes the fact that the number and type of striped files must be the same for each mirror.

BACKUP DATABASE AdventureWorks2014
TO DISK='C:\Backups\AdventureWorks1.bak', 
   DISK='D:\Backups\AdventureWorks2.bak', 
   DISK='E:\Backups\AdventureWorks3.bak'
MIRROR TO DISK='F:\Backups\AdventureWorks1_Mirror1.bak', 
          DISK='G:\Backups\AdventureWorks2_Mirror1.bak', 
          DISK='H:\Backups\AdventureWorks3_Mirror1.bak'
MIRROR TO DISK='I:\Backups\AdventureWorks1_Mirror2.bak', 
          DISK='J:\Backups\AdventureWorks2_Mirror2.bak', 
          DISK='K:\Backups\AdventureWorks3_Mirror2.bak'
WITH FORMAT

 
 

Edit: I’ve made a couple of corrections.

  • Under striping the backup. I incorrectly said that you will not gain any performance if all of the files use the same IO. This statement has been removed.
  • Added a caution when mirroring your backup.

6 thoughts on “RAID your backups

  1. Ron Kyle says:

    In my experience striped backups decrease the restore time.

  2. Raghav says:

    Thanks for the wonderful article for beginners like me.

  3. Perry Whithle says:

    Your description of RAID 0 is incorrect in the context with which you describe. It will not increase risk of corruption, that is possible with any RAID level and is caused primarily by the RAID bios firmware which controls the writes across the disk sets. RAID provides no fault tolerance, that is its downside.

    Also your description of RAID 10 does not really distinguish between RAID1+0 and RAID 0+1, both use mirroring and striping but with different end results.

    Trying to compare backup operations to RAID stripe levels also doesn’t compute, especially where someone writes backups to multiple files on the same disk for example
    Regards Perry

    • Hmm. I’ll admit it’s a laymans view but my understanding was that striping like that increases a risk of corruption because you have an increased number of physical drives that could fail. I wasn’t really trying to differentiate between RAID 1+0 and 0+1. I understand they are different (and even to a certain extent what the difference is).

      Not sure I agree about the comparison though. If you consider a RAID drive == backup file then the comparison works. It’s also a good hook for remembering (again the basics) of both sets of information.

      Great information though! I appreciate the comment!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: