July 18, 2008 at 11:58 am
Greetings all.
I'm a new DBA. I have a background in database design, just little experience on the Administraiton side.
I have a new server that is to be dedicated exclusively as a database server for a particular application. The old one is a bit dated.
I am "stuck" with the hardware. I have to use what I have. I have 2 drives , 10k sas. With these, I have to have the OS and the database.
I will be installing windows 2k3 enterprise x64, along with sqlserver 2k5 standard x64.
There are 2 actual databases in this setup. One is a "configuration" database for the application. The other is a data warehouse for the application. The "configuration" database does not change often. The warehouse is simply truncated and reloaded daily. The data import is about 2GB, with perhaps 15m records across 20 or so tables. The whole process takes almost 2 hours, and I think the limiting factor is that the instance has a single "C" drive on a raid 5 setup.
Back to new implementation. As a note, if the database server totally crashes, rebuilding it is just fine with me; the application is not mission critical.
Now then, I *think* the best thing I can do is to set up the 2 drives in a raid 0 set at the bios level. (Tell me otherwise if I am incorrect). If this is right, is there any benefit to setting up extra partitions for each the OS, trans log, data warehouse, temp, and config database? I don't think this will offer a performance benefit, since this is all going to be on the same 2 disk stripe; however, it may help keep fragmentation low?
Alternativly, I could set each drive seperate, and split up the os,trans,temp,and data among the two drives.
Seeking opinions
Thank you
July 18, 2008 at 12:14 pm
Can you fit the whole thing on a RAID-1 of the two drives? That's the safest. If you really don't care about that, then RAID-0 with multiple partitions will allow you to manage file fragmentation a bit better, which might be a good thing.
I have to say, though, that any data that's worth querying is probably worth safeguarding. So I hope you are at least doing some sort of off-server backups, if you go with RAID-0.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 18, 2008 at 12:51 pm
It can fit on a mirror set.
Since the data is reloaded and retransformed daily, backing it up is kind of moot.
I am most interested in the stripe to increase write performance, especially during the the Data Transform process.
Also, because of this, I run the databases in simple recovery.
Any other recomendations?
July 18, 2008 at 1:24 pm
RAID-0 should do well on write performance, in theory. I haven't used that on a database, for data-safety reasons, but it should be okay. RAID-1 won't write as fast, but will usually read faster.
Because of your dump and load daily, backing up the database indeed does not make much sense. Unless a backup could be recovered faster than reloading the data, if it crashes during the day while it's needed.
I would consider backing up the database structure and code (procs, tables, etc.), but you can do that by creating a script from Management Studio and saving that on a CD or something. That way, you can easily re-create the database and then rerun the load process.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 20, 2008 at 1:31 am
You dont mention how big the disks are but i would opt for a RAID1 config on both drives and install everything to the 1 logical drive.
If the drives are very small i would set them up as just 2 separate disks physical disks (no RAID) with 2 logical drives (C and D)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply