Tuesday, 29 September 2015 00:00

RAID configurations for data recovery in SQL Server

Rate this item
(0 votes)

RAID configurations for data recovery in SQL Server is key defense strategy. Learn about this redundancy hardware approach, including RAID level options, RAID configurations and steps to take from a recovery perspective.

As hardware becomes more and more advanced there are several safeguards that companies employ to ensure their systems stay online. This includes: multiple power supplies, multiple network cards, multiple processors, multiple controllers, hot swap memory, hot swap disks, hot swap processors, clusters, redundant disk storage systems, failover sites and varying RAID levels to support the data redundancy. With all of these measures in place why would your database servers ever go down? That's a good question, but even with the best defenses there is always the possibility of a failure.

From a simple standpoint, not everyone is employing every single technology listed above. From a hardware redundancy standpoint most servers come with multiple power supplies and multiple network cards, but what about all of the rest of the items. The next line of defense is usually with the RAID configuration. In low to mid tier servers, there is usually one controller card and some type of RAID configuration associated with it. As for the database perspective most servers are probably set up to use RAID 1, RAID 5, RAID 10 or some combination of these.

Understanding RAID

RAID (redundant array of independent disks) levels come in many different variations. The most common are:

  • RAID 0 – striping
  • RAID 1 – mirroring
  • RAID 5 – distributed parity
  • RAID 10 – stripe and mirror

There are many more RAID configurations available. Some are practical and some are not. Some only exist on certain vendor products.

  • RAID 0 – This RAID level offers the highest throughput from a write perspective, but there is no redundancy built in. Since your backup files are your last line of defense and there is not a redundant set of data, I would not recommend this option.
  • RAID 1 – This level also offers a high level of I/O throughput as well as a redundant copy. The downside is that the size of the data partition using RAID 1 will be limited based on your physical disk size. One option would be to write your backup file to multiple RAID 1 arrays that are independent of each other and, possibly, to use different controllers or channels to further increase the I/O throughput.
  • RAID 5 – With RAID 5 there is a write penalty because of the need to keep the parity in check. If you are really looking for faster backup processing, this RAID level does not make sense.
  • RAID 10 – With RAID 10, you get the advantage of both RAID 0 and RAID 1. The downside is that it becomes very expensive to implement. But, the upside is your I/O throughput should be very fast. Since RAID 10 uses all of the drives in the array, to gain higher I/O rates, the more drives in the array will increase performance.

Selecting a RAID configuration

When setting up SQL Server, there is no option that checks the RAID configuration type being used, and likewise there are no recommendations on what to use. Most of what you will read often points to the need to have some time of disk redundancy. But as far as what to use – RAID 1, 5 or 10 – this is pretty much left up to the server administrator or the DBA to figure out.

In the good old days when disks were small and RAID technology was not as advanced, the process of getting the best throughput was to have as many disks as possible and to spread your I/O over these disks using multiple filegroups. This is still the case today. But with faster drives, faster controllers and larger capacity drives the configurations are much less complex then they were. Also, when you consider the continued growth of centralized storage systems, much of the control of setting up and maintaining RAID configurations is put in the hands of the storage administrator and not in the DBAs.

So what needs to be done from a recovery perspective?

As mentioned above, SQL Server doesn't know what type of RAID configuration your system is utilizing, so from a recovery standpoint there is no difference when recovering a database on a RAID 0, 1, 5 or 10 array. The one difference you will probably notice is the time it takes to restore the database. As mentioned above there is a level of write speed depending on the type of RAID configuration. Other than the speed of the restore there is not much else that needs to be done from a DBA perspective.

The advantage of using a RAID configuration providing data redundancy is the potential to avoid the need to restore your database. With the use of hot swappable drives or spare drives if there is a disk failure, your system should be able to rebuild the array while the database stays online. Therefore the potential for any data loss is eliminated. That said, it is still important to take backups of your database, because you never know if there could be a complete failure of the RAID array, the server or your data center.

Summary

Although SQL Server does not recommend or really care what type of RAID configuration you are using, you want to make sure you're covered from a disk failure. Select a RAID configuration that provides the data security, but also make sure you have a backup process in place in case there ever is a complete failure. With continued advances in hardware and redundant components you would think you could get away with not doing backups. For the most part this is probably true, but don't get caught without your backups in the event of a bigger catastrophe.

Last modified on Tuesday, 29 September 2015 09:41
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

32 comments

  • Comment Link Paulina Thursday, 08 March 2018 00:03 posted by Paulina

    Hey There. I found your blog using msn. This is a very well written article.
    I will make sure to bookmark it and come back to read more of your useful information. Thanks for
    the post. I'll certainly return.

  • Comment Link Carrol Sunday, 04 March 2018 01:27 posted by Carrol

    So we began wanting into doggie daycare locations.

  • Comment Link Chandra Wednesday, 28 February 2018 19:18 posted by Chandra

    Incredible quest there. What happened after? Good luck!

  • Comment Link Leia Monday, 26 February 2018 02:55 posted by Leia

    Use sterile cotton swabs to apply the tea tree oil.

  • Comment Link Cheryl Wednesday, 07 February 2018 08:31 posted by Cheryl

    Trip in fashion with a Kansas Metropolis limo rental.

  • Comment Link Jacquie Wednesday, 07 February 2018 03:13 posted by Jacquie

    Do you have a spam issue on this website; I also am a blogger, and I was
    wanting to know your situation; many of us have created some nice practices and we are looking to exchange solutions with other folks,
    why not shoot me an e-mail if interested.

  • Comment Link Duane Wednesday, 31 January 2018 22:17 posted by Duane

    Hi there! Quick question that's entirely off topic.
    Do you know how to make your site mobile friendly?
    My weblog looks weird when viewing from my apple
    iphone. I'm trying to find a template or plugin that might be able
    to resolve this issue. If you have any suggestions,
    please share. Appreciate it!

  • Comment Link Ofelia Tuesday, 23 January 2018 18:32 posted by Ofelia

    I'm gone to say to my little brother, that he should
    also pay a visit this website on regular basis to obtain updated
    from newest information.

  • Comment Link Val Saturday, 06 January 2018 00:56 posted by Val

    Article writing is also a fun, if you be acquainted with then you can write if not it is complex to
    write.

  • Comment Link Lynn Saturday, 02 December 2017 05:02 posted by Lynn

    Hello, yes this piece of writing is in fact good and I have learned lot
    of things from it on the topic of blogging.

    thanks.

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.