Hardware design suggestion

I'm implementing a brand new project that consists in a soft-realtime application that executes scheduled operations (tcp commands) stored on a SqlServer 2005 express db. DB and app are always on the same workstation. Let's say that the workload is about 40000 events per day, with periods of activity and more idle periods. The DB is filled by external applications that provides insert/update using ad-hoc sprocs and providing only the 'preedceding event'. The major performance problem I see is given by the fact that each time an event is inserted the sproc must rebuild all the scheduled times (I've build an optimization that uses service broker as to update asynchronously the scheduled times). The application does not requires any special calculation and is very lightweight... it only fetches the db and sends tcp commands. The only 'hard work' is to guarantee the exact time for sending the commands.

Now I have to design the required hardware and I expect some tip from you on how to proceed to build a test workload and how to translate it into 'minimum hardware specification'. Consider that safety is not importand since system are redundant in other ways.
My questions are

- Should I put OS, tempdb, database and logs each on a separate disk array ? My idea is to have a two-disk RAID0 for OS and logfiles, and a n-disk RAID0 for tempdb and appdb

- How to extimate the number of disks to have in the RAID ?

- Should I also consider processor ? I have the idea that processor is not very important while I should pay attention to bus and memory bottlenecks: so I've thought to use DDR2 RAM with a FSB of 1066 MHz and a single dual or quad core cpu, so discarding XEON multiprocessor systems that requires FBDIMM, that AFAIK are slower that DDR2.

- any other suggestion ?

The process you are describing sounds like it could be very intensive. You may want to have others take a very close look at your design to make specific feedback on ways you could make this more efficient.

Regardless, it is very difficult with the information provided to give you a hardware recommendation. One thing I can recommend is you avoid the use of RAID 0 for this. With RAID 0, a single disk failure will cause you to lose the array.

Good luck,
Bryan Smith

|||

I would not use RAID 0 for anything for a project like this. If you lose any disks in a RAID 0 array, you are down.

I would put OS and page file on RAID 1, TempDB on a separate RAID 1, AppDB on separate RAID 10 or 1, and Log files on a separate RAID 10 or 1.

The Intel Tulsa MP 3.4 GHz Xeon (with 16MB of L3 cache) performs very well on SQL Server 2005. If you don't need four CPU's, I would look at the newer, Core2 Duo based CPU's, like the Xeon 5160. Make sure you have enough RAM to ensure you don't have memory pressure (which will also reduce I/O pressure). I would suggest 4GB minimum.

|||I see that both of you have suggested to avoid RAID0, and I want to clarify a point: in our system performance is more important than security on a single machine since the system is fully redundant in the sense that we have more machine that runs in parallel, each one with the same scheduling and performs the same task, plus an 'arbiter' that selects the active machine. So If I loose a disk I have another machine that is fully mirrored to the first one... a sort of RAID1 between machines.

Anyway I'm building a test environment and I expect some sort of failsafe RAID on production machines.
|||

If you have that kind of money to spend, go with RAID 10 (1+0). Even with what you have stated above, I would not recommend RAID 0.

B.