Database Server Specification And Configuration 101

Besides my day job as a Database Architect at NewsGator Technologies, I also teach classes at University of Denver- University College, which I have been doing since 2000.  The fall quarter started last week, and I am pretty busy teaching a  class on Database Fundamentals, another on Software Engineering, and two sections of a class on SQL Server 2005.

The first assignment that I had my students take on in the SQL Server 2005 class is shown below:

Your assignment is to go to the Dell website, and configure a rack-mounted server to run the x64 version SQL Server 2005 for an OLTP workload. You can assume that you have a high performance fiber-channel SAN available to use as you wish. You have a $20,000.00 hardware budget for this server.

You need to consider how many and what type of processors, how much RAM, how many internal drives, whether you you need a hardware RAID controller,etc.

Your deliverable is a Word document that will list all the components (such as server model, CPU(s), RAM, etc.), and how you will configure this machine to run SQL Server 2005. You need to decide what version of Windows Server you will run, and how many drives you will need on the SAN, along with their RAID level and what they will be used for.  You should try to configure this server to minimize your SQL Server license costs.

You should justify your decisions as best as you can. Good luck!

There is no one right answer for this assignment. Instead, it was meant as a “thought exercise” to get the students to consider how to attack this problem. I also wanted to see how their choices were justified. The $20K budget was for hardware only, not for OS or SQL Server licenses. The assignment also spelled out that a “high performance fiber-channel SAN” was available, which greatly simplifies the I/O situation on the server itself.

One important thing to consider here is how SQL Server licensing works. If you choose processor licensing, it is done by physical CPU socket, not by CPU core. This means that you should always choose quad-core or better processors instead of single or dual-core processors to minimize your SQL license costs.

Especially with Intel processors, you want to get processors with the largest L2 and L3 cache that is available for that processor. Large L2 and L3 caches have a huge affect on SQL Server performance.

SQL Server 2005/2008 loves RAM. The more the better. You should basically get as much RAM as you can afford or will fit into the server or is supported by the operating system that you are using. Fortunately, RAM is pretty inexpensive right now, and 4GB sticks have come down to reasonable pricing levels.

Right now, (since Sept 15, 2008), the sweet spot in the Dell Server lineup for this assignment is the Dell PowerEdge R900 server, since it has four CPU sockets and supports the new Intel Xeon 7400 family of six-core processors. It also has 32 memory slots, which let you go up to 128GB with 4GB sticks of RAM.

Here is how I would spec and configure the R900 server:

(2) Intel Xeon X7460 six-core 2.67GHz, 16MB L3 cache, 1066MHz FSB processors (leaving two empty slots)

(8) 4GB sticks of RAM (leaving 24 empty slots)

(2) 73GB 2.5” 15K SAS drives in RAID 1 for the operating system and the SQL Server binaries

(1) Integrated internal PERC controller for the internal drives.

(1) QLogic 2462 Dual-channel 4gb Optical FC HBA to talk to the SAN

I would choose Windows Server 2008 Enterprise Edition x64 for the operating system, and I would want SQL Server 2008 Enterprise Edition.  I would want the data files, transaction log, and TempDB files on separate RAID10 LUNs on the SAN. Depending on the SAN, I might want multiple LUNs for each file type. The number of spindles in each LUN would depend on the the workload, but the more the better.

This pretty much maxes out the $20K hardware budget (after backing off somewhat on the Dell support level). Faced with choosing between cheaper processors or more RAM, I chose the better processors in this case, simply because I can add RAM later (since I have 24 empty slots), much easier and cheaper than I could upgrade the processors.


Technorati Tags:
This entry was posted in Teaching. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s