SQL Server Performance Forum – Threads Archive

IO Server Configuration

Hello there, I have a question about the IO configuration of our database server we are about to setup. I’m mainly concern about getting the maximum performance from what hardware we have. Since we dont have a gazzillion dollars to buy a san, we have to make due with very little. Plus i’m not a DBA, i’m a developer trying to fill extreamly large shoes! This is what we have as far as IO hardware: 2 onboard Raid Controllers (SATA)
1 internal Raid Controller (SATA)
1 external Raid Controller (Direct-Attach)
1 external USB Drive (USB) btw, we are installing SQL Server 2005 Standard edition on this server. We have a single user database, plus all the systems database. From our user database, we have 1 table that is extensivly used (inserts, deletes and Selects…no updates), let’s call it MAINTABLE. This single table continually increases in size constantly, and this will be the model forever. Currently we have about 10 millions inserts monthly. But this number increases for each new client that gets added to the system (About 25,000 records per client monthly). Now from what i’ve read in books and articles online, i need to seperate the logs from the Data files. I want to go even further and seperate MAINTABLE from the rest of the user tables. With such large file, our indexes on that table are also pretty large. Currently our database is about 6Gigs, and the indexes for MAINTABLE are about 4Gigs all to themselves. I also want to do my backups on a HD to improve backup and restore performance. Now with all that said, this is now i had planned to seperate and partition my HD’s. onboard Raid #1 Raid 1
C: Operating System
D: Misc Files
E: System Data Files onboard Raid #2 Raid 1
F: My User Database Log file
G: All other Log files Internal Raid Raid 10
H: MAINTABLE Data file External Raid Raid 10
I: My User Database data files
J: MAINTABLE Indexes
K: All other Indexes External USB USB
L: Backups & misc
Now, my problems. I’m not sure if this design is awful, ok or good, i’d like comments on this. I’d also appreciate for some pointers or re-arrangements of this design. My other problem is that from a recent book i started to read, they mention that the tempdb database in SQL Server 2005 requires greater performance from the IO side. Currently it resides on a onboard Raid 1 Controller, with the OS and the rest of the System data files. Is this enough, or should i move things around? This kinda of hardware / DB design is way passed my current knowledge, i’m also wondering if i need to seperate the files more or less with HD partitions to prevent file fragmentation. Which is a concern for us since the database will never stop growing in size. All i can do is set the size to 3 or 4 times it’s current size, but eventually the growth will catch up and we will need to autogrow the file and fragmentation will start. Anyways, pretty much any advice will be sooo appreciated, so feel free to tear down my design 😐 Thanks,
Chris

forget the SAN,
go with SAS drives
do not use SATA for production transaction processing SATA is ok for archival data and testing

Joe, can you give us more information on your above recommendation? —————————–
Brad M. McGehee, SQL Server MVP

on what point
SAS vs SATA
this is entirely due to 10 & 15K drives being designed & manufactured to server specs
5 year life, 100% duty cycle
7200 rpm SATA to consumer specs, 2 yr, 20%
if seagate has SATA drives spec to 5 yr, 100% then ok

Thanks for the input, I got similar recomendations from a tech support line for ordering server hardware. They told us to go scsi for the drives instead of SATA, as we would have less trouble with the SCSI drives in the long run. Hardware wise i’m pretty much set, advices have been reflected upon and decisions have been made…oh and budgets have also been exceeded <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> but that’s another challenge! My concern is still present as of the way i should seperate my arrays/drives, which files should i put on which arrays/drives and which files requires the best performance? Taking into account the description i presented in my first post, which file requires the best performing array? We’re probably going to buy an external SCSI 12-bay enclosure, but we dont have enough money for using all 12 bays. We’re only gonna be using 4 of them, the rest will be there when we have money to upgrade. So we’ll have 1 array on that SCSI device, we’ll also have our old SATA array (that we still have to use because of buget concerns) and 2 arrays will be onboard SATA arrays. So our best performing arrays in order will be: 1. SCSI 12-Bay external enclosure 2. Internal SATA Array 3. And the 2 onboard SATA arrays which files requires the best performance, so which files should use which array? -Transaction Logs -Indexes -My MAINTABLE data file -tempdb data file regards, Chris

]]>