MSSQL 2008 Setup 1

[crmayer]

Hello,
I was looking for advice, I have googled this topic for the last week, finding a lot of information, but not exactly what I was looking for.

We are getting ready to upgrade our TMS software and I am looking for advice on how to allocate different RAIDS/disks for MSSQL.

This is what I am working with:
Dell Server with 8 - 146GB/15k drives
Attached to MD1000 with 15 - 146GB/15k drives

Now, initially what I was looking at was breaking this up as follows:
Server
RAID 1 (2 disks) - OS
RAID 10 (6 disks) - Application DB Primary Storage Group
External Storage
RAID 10 (4 disks) - Application DB Secondary Storage Group
RAID 10 (4 disks) - Tempdb and temp log
RAID 10 (4 disks) - Application transaction log
RAID 1 (d disks) - MSSQL System DB's

There are a ton of sites that talk about the tempdb, but nobody says directly if you should split up the tempdb and the tempdb transaction logs.

Also you can read one site that tells you one thing, and the next site says a different setup.
I am sure there are a ton of different configurations, all of which have some merit, therefore I am just looking for advice and options for the optimal performance possible, without losing redundancy.

I have also looked at maybe putting the temp db on a RAID 1 a crossed 3 disks, I know I lose the redundancy doing that, but it is the temp db and this might give it added performance.

I am open to any and all ideas, and I know that RAID 10 takes a lot of disks, and I hate to say that money is not an object, because it is to a degree, but I do not want to be addressing this issue 6 months into our conversion, so I will spend what I need to do this right the first time.

Thanks in advance for any thoughts or advice.

 

[crmayer]

I am sorry, I stated I have thought about a RAID 1 with 3 disks, I meant to say a RAID 0.

 

[mrdenny]

Never use RAID 0 on a SQL Server. If you loose any of the three disks the SQL Server will not be able to run until you replace the disk, resetup the array and recreate the folder that the tempdb database sits in.

Your config looks fine. There isn't a need to put the transaction log for tempdb on its own disk until you will be hitting it a lot.

Do you really need RAID 10 for your main databases? What sort of IO requirements do you have? Is your database mostly read or mostly write?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[crmayer]

I have always been a fan of RAID 10, I guess I feel it gives you the redundancy that allows you to sleep at night, while not taking a hit on perforamce.

Is there something that you recommend instead?

I would say more read then write, but we do have a lot of writes also, if I had to guess I would say 60/40.

As for specific IO requirments, I can not answer that one. Our vendor does not get too specific with the exception of saying if you have 50 or less users, use this config, if you have more then 50 use this one.

They do recommend that we have two servers (application and database), they also recommend that we have external stroage. I believe they recommend the DB to be setup:
RAID 1 for OS
RAID 1+0 for transaction logs (RAID controller 256MB cache)

RAID 1+0 Primary DB
RAID 1+0 Secondary DB

They are pretty brief. We currently run a similar setup as to above for an older verison of thier software, and the performance is not that great.

 

[mrdenny]

Without getting accurate requirements from the vendor you won't be able to correctly design a storage solution.

Unless you have high write IO requirements RAID 10 is just overkill and a waste of cash. You'll actually get better read performance from a 6 disk RAID 5 array than a 6 disk RAID 10 array because the RAID 5 array will have access to 5 (or 6 depending on how parity is written) disks when reading, where the RAID 10 array can only use 3 of the disks at most.

What's the current IO load on the existing system according to PerfMon?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[crmayer]

From what I am reading them, RAID 10 might be best for the tempdb and our secondary storage group. Our vendor wants the seconday storage group to move some tables over to that are being wrote to all the time.

I will see what I can find out as for as the IO load.

I understand that some think RAID 10 is overkill and even a waste of money. I am no expert when it comes to RAID configs, but from what I read when I am trying to research them, RAID 10 is the best performace. But, then again, I can find other articles that say different. Just depends who you talk to or wha tyou read.

 

[crmayer]

If I am looking at the right thing, it would look to me like the read/write would be about 4 to 1.

 

[mrdenny]

RAID 10 is best for heavy writes as there is no parity calculation. However RAID 5 is best for heavy reads as you get more disks to handle the read requests.

The ratio of reads to writes isn't that important. You need the actual numbers. The system needs to be able to handle n write operations per second, and n read operations per second.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog