Data Warehousing and Business Intelligence Even an SMB Can Afford

In this Recipe, I'll show you how to create a cost-effective server to run data warehousing and BI applications with inexpensive, off-the-shelf hardware and software. For less than $7,500, you'll be able to offer SMB customers with up to five client machines a complete solution that will perform admirably. If this sounds a bit expensive, keep in mind that a large enterprise could easily pay more for the BI software alone.

Summary of the Technologies

The software market for BI applications is dominated by specialized vendors selling expensive programs. Companies that include IBM and SAP have traditionally served this segment by offering solutions that start at tens of thousands of dollars.

But Microsoft has gotten into this market, too, with its release of SQL Server 2005. There are many benefits to using Microsoft's software. For instance, Microsoft's offerings can be loaded onto standard Intel hardware with the Windows operating system, thus eliminating the need for more-expensive Unix machines. Also, recent advances in chip-manufacturing technology mean SMBs can now afford 64-bit processing capacity for addressing more than 4 GB of system memory and accessing larger files. Both these software and hardware elements are key to realizing the benefits of BI. In essence, BI is a collection of software applications, and they require large amounts of memory and storage space.

Also, there's been an important paradigm shift in the hardware market. The revolution in storage components and storage networking has opened up a new world for SMBs that was once too expensive for them to realize. More, specifically, the advent of RAID and the resulting convergence of ATA and SCSI technologies evidenced with the SAS standard are of paramount importance. These can help solve the storage needs of SMBs that want to do data warehousing and run BI applications.

Ingredients

For this Recipe, I recommend you use Microsoft server software, AMD Opteron chips, a Tyan motherboard, and Maxtor hard drives:

* Software: My OS of choice for this system is Microsoft Windows Server 2003 R2 Standard x64 Edition. This gives us a good platform for Microsoft's SQL Server 2005 Standard Edition X64, which is optimized for 64-bit processing and memory accesses.

Optimization for the newly available multi-core 64-bit x86 CPU architecture is essential for extracting maximum performance from every dollar spent. This software meets that need very well. Manufacturer's suggested retail prices (MSRP) for these software packages are $1,029 for Windows Server 2003 and $1,849 for SQL Server 2005. For more on SQL Server 2005 Standard Edition, see this Microsoft SQL Server Features Comparison page.

* Hardware: Here we'll create a server based on two dual-core AMD Opteron 200-series CPUs—the 265 to be exact. AMD has traditionally competed well with Intel chips on pricing, and its Opteron chips are generally regarded as superior to Intel's Xeon offering in the dual-core 64-bit arena. Again, we are aiming at the best possible performance-to-price ratio, and AMD really delivers. The MSRP is $400 a piece, which includes both the chip and an AMD-approved thermal solution.

* Motherboard: Our pair of Opterons will be mounted on a Tyan Tiger K8SSA S3870G2NR ATX motherboard, which retails for about $360. This low-cost component has several features that make it ideally-suited for this project, including dual CPU sockets and six memory sockets that we'll populate with four 1-GB registered ECC Kingston ValueRAM modules, at $200 each. Probably the best value-adding feature of this motherboard is the SO-DIMM slot that accommodates a low-profile RAID controller, which Tyan calls a "TARO" card. The M9000-10 is an 8-channel SAS RAID controller card, and it sells for a little over $100.

* Storage: Our storage duties will be handled by four Maxtor Atlas 15K II 73.5-GB SAS hard drives, which sell for about $400 each. We'll mount them in an Adaptec 335SAS internal enclosure (MSRP: $369). Our bootable array will be a mirrored pair of Maxtor MaXLine III 250-GB internal drives, which I chose for their high reliability and fine performance; they sell for approximately $100 each. All these assemblies will fit nicely inside a sleek, black Antec Titan 550 Tower Server Case, which retails for about $230. Five Steps to Building a Data Warehousing Server

1.) Assemble the Motherboard "Combo": I like to start by putting together the motherboard, CPUs, and RAM. The retail box CPUs come with their own heat sink, but I recommend using a bit of Arctic Silver thermal compound to help with cooling. For precautions and recommendations for this product, see the company's Application Instructions page.

Tyan's Tiger K8SSA motherboard has six memory slots: four for CPU0, and two for CPU1. This design uses a dual-channel memory controller, so we'll use two RAM modules for each CPU. For proper placement, use the black memory slots for the first pair of modules per CPU, leaving the blue slots empty. In the following illustration, I've added two red arrows that point to the slots the memory modules should be inserted into. I've also added one blue arrow on the lower left to indicate where the M9000-10 TARO card plugs into the SO-DIMM slot (it's between the first 32-bit PCI slot and the 64-bit PCI-X slot):

2.) Install Motherboard Combo Into Chassis:Once you have the motherboard combo assembly ready, lay it aside on an anti-static mat. Next, prepare the chassis. To do that, you'll first need to put the I/O shield and standoffs into place. Then lower the motherboard assembly down into the chassis. Screw it into place, making sure to use insulating washers between the screw heads and the motherboard; this will protect against premature failure and ensure longevity.

3.) Install Drive Cage and Drives Into Chassis: You will need to put the cage into the chassis before you put the drives into it. If possible, resist the urge to put the drives into the cage first and then set the whole thing into the chassis, because the hard drives are sensitive to shock and can be damaged if you drop or bump them while putting the system together. For more detailed instructions on how to install the Adaptec 335SAS enclosure with Maxtor hard drives, see my related TechBuilder Recipe How to Build a RAID-Capable Storage Server.

4.) Create SATA Boot Array and Install Operating System: Once you have finished the usual processes for assembling the computer system (such as installing the other disk drives, attaching cables and connectors, etc.), you can move on to the easy part—installing the software. By using separate arrays for the OS and the data warehousing, you'll improve the reliability and performance of the server.

Now it's time to create a RAID 1 array (mirrored pair) from the two Maxtor MaXLine III drives by using the motherboard's integrated SATA RAID controller. First, you'll need to make a driver disk. Tyan has included a handy utility for this task on the CD that comes with the motherboard. Upon rebooting the system, you'll have a perfect opportunity to check the BIOS and firmware revisions to make sure everything is up-to-snuff. For help with this task, I recommend you read Tyan's Tiger K8SSA (S3870) BIOS information page.

Once you've created the array and initialized it, you can then install the OS and application software program files onto the array.

5.) Create SAS RAID Array and Install SQL Server: The TARO card controls our SAS drives, so now we'll create a RAID 10 array (a striped set of two mirrored pairs) with it. A RAID 10 array has several benefits that are well-suited for our application. First, it's cheap to implement, because it does not require an expensive controller capable of performing parity calculations associated with RAID 5. Second, in terms of performance, RAID 10 is better for the type of data accesses we'll be doing with BI software. And third, a RAID 10 array will tolerate multiple hard-drive failures within the array, provided only one drive in each mirrored pair fails.

There is a popular belief that RAID 5 is "better" than RAID 10, but that does not always hold true. Comparing our RAID 10 array to a RAID 5 array with four of the same drives, we have the same read performance, slightly better write performance, superior fault tolerance, and lower cost. For more on the benefits of using RAID 10 over RAID 5, see Adaptec's "Which RAID Level is Right for Me?" page.

As before, you will need to load the drivers for the controller. Then you'll create and initialize the array. This can be done after the OS installation, since only your data warehouses will be stored here. Once you have this array ready, you can install the SQL Server software on your SATA array and point your databases toward the SAS array.

There you have it: a complete, under-$7,500 solution that will perform admirably for a SMBs with no more than five client machines. The server is also upgradeable and reliable—two important qualities customers are always looking for.

DAVID GILBERT is the owner of Appalachian Computer Systems, a West Virginia system builder that specializes in multiprocessor SCSI RAID servers.