Custom SQL Server Computer Build

Question

I hope this is the right place to ask this question. I'm a user at StackOverflow, and when I perused the StackExchange websites, this was the best match I could find. So, if I've posted in the wrong place, please let me know and I'll move the post.

I'm working with a group of people and we're creating a start-up investing firm. I'm the "tech guy" in the group (I can build PCs and program), and so it's up to me to figure out what we need for our database server.

1. We're going to use SQL Server because it's what I know (so, please don't suggest MySQL, Oracle, etc...).
2. I'm familiar with purchasing components and assembling a PC, although I've never done it specifically for a high-performance database server before.
3. Assume we have a several thousand dollar budget (my co-investers are doctors).
4. Assume we have a constant stock market data feed (something like 100 stocks at a time) that needs to log the data to a database as it comes in and also use the current data to query for similar past data and perform comparisons.

What I'm mainly interested in is where to spend more money and where additional power is less useful.

For instance, when it comes to drive set up, I was thinking of something along the following lines:

1. For the OS and SQL Server application installation, a RAID 1 array of 2 60 GB SSDs.
2. For the SQL Server data volume, a RAID 5 array of 4 medium-sized, fast HDDs (I'm thinking 300GB Velociraptor drives; So far, 9 YEARS of data takes up only about 100GB).
3. For the SQL Server log file volume, a RAID 1 array of 2 small-to-medium-sized, adequate HDDs (non-Velociraptor here).
4. For the SQL Server temp database volume, a RAID 1 array of 2 60 GB SSDs.

The OS/SQL Server application volume will be a software RAID using the motherboard; the rest of the RAID arrays will be controlled by a dedicated RAID card.

From what I've read online, the above setup is likely to give me great performance and stability.

Now, when it comes to other components, what's necessary?

1. Is the latest i7 Quad Core useful for this, or is that overkill with processing power? Can I get away with an i5? An i3? A Core 2 Quad?
2. I'm assuming that a 64-bit OS with 64-bit SQL Server is better because it will allow me to go beyond 4GB RAM. If that's true, is 8GB enough or do I actually get performance benefits at 16GB? Does DDR3 RAM offer significant performance enhancements?
3. I know that the graphics on this machine are extremely unimportant, but would a dedicated graphics card off-load work from the CPU, increasing performance, or is that a waste of money?

Most of the work I've done with SQL Server has been for small companies. As a result (due to price), we've mostly done installations of SQL Server Workgroup. For everything I've ever done, that's been just fine and dandy, and I'd like to continue using that version. Is there any practical benefit to upgrading to Standard?

Now, I'm guessing that for our needs, a server like this will cost about $5,000-$6,000. Am I better off buying the components myself and building the system or buying a server from Dell/HP?

Also, (and I know this is a big wrench in things), if, during the initial, testing phase, we decided that we didn't want to spend $6,000, what cuts would you make to bring this down to a $2,000 system?

Answer 1

This is essentially a hardware capacity planning question. The problem with them is that if you give enough details that we can tell you what hardware you need, it'll become "too localized" to your particular set of circumstances. That said, we've got a number of pseudo-answers already written to help you:

Can you help me with my capacity planning?
How do you do Load Testing and Capacity Planning for Databases
How do you do Load Testing and Capacity Planning for Web Sites

Those are from Server Fault's list of Canonical Question/Answers.

They're not all directly related to what you're asking for, but tangentially similar. It boils down to the fact that this is your job; if you want to get it right you really either need to learn how to do it, or find a consultant who does.

One last thing; the TCO of building your own server from components is always higher on the average than buying a prebuilt server from HP or Dell. I happen to prefer HP, but the SE Sites run on Dell and the site seems to work quite well. It will be cheaper up front to build your own; but warranty, compatibility, scalability, and support will all chew that cost savings like a hungry dog with a fresh steak.

Edit:
Since you're discussing RAID levels in the comments above: What are the different widely used RAID levels and when should I consider them?

Answer 2

+1 for the answers from @CadeRoux and @ChrisS, they make valid points.

Your comments to those answers highlight that this is essentially a proof-of-concept venture at this stage and you want to minimise your capital investment. If that's the case, forget spending $1000s on hardware and licenses, rent.

You don't appear to need to store a vast quantity of data, so your early build-deploy-test cycles may target subsets of the 100GB (after 9 years) that was mentioned. Even at 100GB you should still fit comfortably into a wallet friendly Amazon EC2 instance. You can technically/legally deploy a developer SQL license to a bare EC2 VM while you're pre-production, cutting costs further.

Brent Ozar posted an interesting analysis of SQL Server on EC2 last year that would help you weigh the pros and cons. I'd also weigh the merits of SQL Azure, especially now that the tooling (SSDT) is better geared toward the platform.

Answer 3

Based on everything you have said in the question and the comment, I don't think you need to worry about hardware yet unless you are trying to just get a ballpark price estimate for feasibility.

Since you are a software person, I would build the prototype on commodity hardware like your ordinary laptop, analyze and understand the problem and then spend money once you know more about the profile of the software and the problem space. If your laptop is not up to it, then pick up a refurbished server for a few hundred dollars to get it closer to being able to test. It sounds like a small amount of data and there is no indication of the large amount of ongoing data analysis (which can greatly be affected by the data model - some data models can make analysis many orders of magnitude faster).

If you are trying to test at production loads, you will need a production spec machine - so the problem is a chicken and egg one. To have a production machine, by your own admission, you would need an expert.

And I know this is relatively off topic from your question, but:

The things you need to be looking at on your software design are the rate at which the data is coming in, the amount of processing (parsing, de-duping) you will need to do, the model for the data, the size estimates, the way the reads are going to work (whether you have multiple models - one for writing and one for reading, like data warehousing), and the complexities of the analysis and whether this will be performed by SQL Server in your architecture or by client code (and whether you intend the client code to run on the same server or whether you will also have an application server). This will tell you a lot more about what you want out of your SQL Server than your point 4 (the only thing you have given us which has anything to do with determining the nature of the server configuration you will want in addition to the $2000-$6000 budget). And this would be information that your expert would need to be useful to you.

In my experience, the right time to make these kind of decisions is as late as possible - and a decision on this hardware can be deferred.