RAID configurations for data recovery in SQL Server

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.

This Content Component encountered an error

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. To learn more, read this article about different RAID levels.

  • 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

Learn more on this topic:

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.

ABOUT THE AUTHOR
Greg Robidoux
is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
Copyright 2007 TechTarget

This was first published in January 2007

Dig deeper on SQL Server Backup and Recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close