Big Data

A Low Cost Data Warehouse Using SQL Server

Date Added: Jul 2009
Format: PDF

Data Warehouse (DWH) architects often wrestle with the challenge of creating a DWH that is cost effective, yet uncompromising on performance. Most data centers now have a mix of OLAP (Online Analytical Processing) and OLTP (Online Transaction Processing) databases that differ on the performance and criticality requirements. While the expensive OLTP databases are customer facing, they have their own requirements of recovery point, recovery time, and disaster recovery; the simpler OLAP databases are designed for easy retrieval of data. The differences make a strong case for segregation of the databases. With separate DSS (Decision Support Systems), the costs are also higher. A Microsoft SSIS (SQL Server Integration Services) is a powerful extraction, transformation, and loading tool that come bundled with the MS SQL Server 2008. This can help move Online Analytical Processing (OLAP) databases to a Decision Support Systems (DSS) environment. Using MS SQL with Dell hardware, a powerful, yet cost effective SQL server can be built. The Dell PowerEdge Server T710 has a 64-bit extended memory and up to 96 GB of RAM and can accommodate up to two dual or quad core processors. The PowerVault MD1120 SAS storage array can be expanded to 144 drives behind a PERC6/E RAID controller that is capable of expansion for up to 21TB per controller. This hardware configuration combined with MS SQL software can together form a low-cost and efficient enterprise DSS that deliver on its performance promises.