Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

Disk Partitioning for Database Servers on W2003

Locked

Hi all

Just wanted to get some opinions on disk partitioning for a DB server Im implementing

I have a HP Server with 6 drives and an array card. Im going to setup one array as a mirror for two disks for the OS/Transaction log and another array as a RAID5 set of the other four disks

Ive read a lot of stuff about splitting your OS and you data accross two arrays etc improves performance rather than having Windows see all the disks as one big array but what Im wondering is are there any best practices with regard to partitioning the RAID5 array for a DB (SQl Server) implemention?

Our previos standard would have been to create 2 windows partitions within the RAID5 array, 1 for data, 1 for Apps. However this server will only house SQL Server so is there any advantage to multiple partitions for the SQL Server apart from putting transaction.log on a completely different partition

Many thanks

Topic

SQL partitioning

In reply to Disk Partitioning for Database Servers on W2003

If you can do the following, it would increase perfomance, reliability, and easy disaster recovery.
1)OS- Win 2003 in your case across 2 drives mirrored
2)SQL databases stripped with parity RAID 5 across 3 or more depending on space currently needed and future planning.
3)Transaction logs- mirrored across 2 drives

This is my current configuration and what a difference it has made with response times. I have given myself room for growth with this type of confiuration, easy recovery, and the performance outstanding. I hope this was helpful.

Good strategy

In reply to SQL partitioning

This is a good strategy. However, if you want to extend the data to another volume. it is a painful process unless you take advantage of mount points. SQL Server 2005 supports mount points.

RAID 10

In reply to Good strategy

For better performance go with a raid 1 for the OS and a raid 10 for the SQL Data and logs.

RAID5 Not the Best for Databases (OLTP Anyway)

In reply to Disk Partitioning for Database Servers on W2003

Most folks know about RAID5 because it is the lowest-cost, fault-tolerent array. With n disks, you get n-1 disks of storage.

For databases, however, RAID5 is not the best performer. The reason is the read-after-write penalty.

Virtually all database I/O is update activity at the OS level. Data files are preallocated, so even an insert on a table is really an update on the data file at the OS level. Once a block is updated, the whole stripe that contains the block has to be read so that a new parity for the stripe can be calculated and written. That is extra I/O.

For a read-only database or a data warehouse where you are not too fussy about the nightly load, this may not a problem. For a high-volume OLTP database, this can be a killer.

I have found 30%+ improvement by migrating SQL Server databases from RAID5 disk to RAID1+0 (RAID10) disks as was recommended.

Partitioning Transaction Logs

In reply to RAID5 Not the Best for Databases (OLTP Anyway)

I guess I never directly answered the question about partitioning off the transaction logs.

I used to just say that was great. After all, a busy database will have lots of log writes, lots of data file writes, and lots of checkpoints. Why not separate the I/Os?

However, if this is a database you care about, you are probably committed to something close to point-of-failure recovery. As such, you are probably dumping the transaction log throughout the day. The I/Os needed to read the transaction log for dump will compete with the other database I/Os.

With a mirrored pair for transaction logs, you have 2 spindles to handle regular activity and the dump. If you leave the transaction logs with the data files, you have all n spindles available. Basically, you dilute the pain.

RAID1 for Performance

In reply to Disk Partitioning for Database Servers on W2003

Use RAID1 for performance as it can read from either disk and dosen’t have to calculate parity. Add another mirrored RAID1 set into the equation on another SCSI HBA or channel and you have created a mirrored, duplexed array.

RAID5 is not recommended for SQL servers due to the volume of transactions expected. There is simply too much overhead to get good performance for a database with high transactions.

I never partition an entire array as one partition; always subdivide. There will be many that dispute RAID 1 as a better performer over RAID 5. Without getting too technical do your research on how the OS handles mirrored and duplexed drives in a RAID 1 configuration. I am familiar with Novell Netware and how they optimize RAID 1. I can not vouch for Windows or Linux implementations and technology. In Netware it can read from either mirrored set (duplexed or not) thus increasing performance.

RAID 5 for a DB server (don’t)

In reply to Disk Partitioning for Database Servers on W2003

Damon,

We use a bunch of Progress and MS-SQL database servers in our companies and by experience I can tell you that RAID 5 is the worst you can do for a database server. The options you have are using RAID 0 (striping) very fast but no reduncy and even worse a higher risk of failure.

RAID 1 Mirroring Writes are a bit slower reads are spread over multiple disks and if the administrator/engineer uses a smart seperation of data and logs over different mirrored sets performance is good.

RAID 10 (Mirrored Striped Set of disks). Only works for systems with 4 disks or more. Writing in four disk set is about as fast as a on striped dual drive storage. Reading is even faster.

Current RAID controllers support RAID 10 very well and use quite a lot of Cache Memory (battery backed up)

RAID 10 is by far the best solution for a DB server and your 6 drive system is “ideal” for that. You should consider to use a RAID 1 set for the OS and possibly use the remaining space for transaction logs. The remainig four drives in a RAID 10 config used for data will give you very good perfomance.

Hope this will help you to start of on the right foot.

Gert-Wim Scheppink

It is not complex

In reply to RAID 5 for a DB server (don’t)

I think everybody worry too much about raid, in fact it is detected as physical hard disk, the difference it that raid card transter data from physical disks to the OS, or OS to disk. So it is not hard to operate on raid, but generally raid is used for important data, there mustn’t be any error.

I recommend using partition master, it support raid perfectly and you can copy the drive or partition to backup the data. The most important, it can partition raid easily and safely. this article may help http://www.partition-tool.com/resource/partition-raid-windows-server.htm

[Author]

Replies