1

I'm quite a novice at configuration of MS-SQL so would appreciate some advice. I've got a Dell PE2950 Twin Quad Xeon server with 32GB RAM running MS-SQL 2008 Web Edition.

The drives are 4x73GB SAS RAID 6 and 2x160GB SSD RAID 0.

Given that I cant change this drive configuration what would you recommend in terms of db/log files, system table and tempdb?

Thanks Dean

2 Answers 2

2

I would put everything on the RAID 6. And I'd go on record as not liking the weird RAID setup.

The RAID 0 pair has no redundancy. I wouldn't trust it in a production environment regardless of whether it has SSD or old-school disks.

I hear one or two scary stories involving sudden, early drive death about SSD every month, which just makes it that much more worrying. Often, it is consumer-level kit. I'm sure that a few reading this have had great experiences. But...if it is a revenue-generating site or a public-facing site, it just isn't worth my job. Another thing is that a SSDs are probably totally overkill, considering that the RAID 6 probably can't read as fast as the SSDs can write.

If the pair of SSDs were a RAID1, that would be different. I'd stick the log files (for all of the databases) on there, and maybe tempdb itself.

Since you can't change the RAID setup, I don't see that you have any choice.

But it's not really that bad, if you have all of that RAM to yourself.

Assuming that you aren't running any other heavy software on that box, since you have 2x 9 GB databases and quite a bit more memory than that, all of your data will (eventually) be cached in RAM. Once the data is in memory and assuming that you don't do lots of large writes to tempdb (which an OLTP database generally shouldn't do), the speed of your drives isn't as critical. The big exceptions being log writes and log backups (which read the log files), if your site databases are running in FULL recovery mode. The RAID6 is going to be (relatively) slow for writes and I'm guessing that the 73 GB drives aren't 15KRPM disks, but that volume should be able to handle a few MB/second. The rate at which you need to write to the log files is determined by how busy your site is and how efficient at writes it is.

The system databases (master, model and msdb) are not large and should get hardly an I/O, compared to your user databases. It won't significantly matter if they are also on the RAID6.

I might consider using the RAID0 pair to hold backups, assuming that they get copied to somewhere else in reasonably short order, but that SSD RAID pair seems like a real waste of money.

When you do get the system up, make sure to monitor the load on the storage subsystem to make sure that it isn't over stressed. If it is, you may have to change the storage configuration to make your site perform better.

2
  • Well thought out and stated answer.
    – mrdenny
    Jul 7, 2011 at 23:42
  • Thanks - Really useful. I've gone back to my hosting company to see what it would take to change the SSD's to RAID1 Jul 8, 2011 at 12:17
0

Without an accurate picture of the type of data, type of queries, size of tables, whether or not index are being used, foreign-keys, how much RAM is being dedicated to mssql, what else is running on the box, etc... there's very little that could be said. In a properly functioning system... very little is being done from disk until some sort of write operation is done.

There's a reason DBAs make a lot of money doing their job... And there's a reason there are dozens of different "types" of sql servers out there. There is no generic 1-size-fits-all answer here.

1
  • I cant answer all those questions but I can a few if it helps. Nothing else is running on the box, its a dedicated SQL server. There will be 2 databases of about 9GB each which are the backend to a very busy discussion forum/blog system. Jul 7, 2011 at 21:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .