The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

4.2. System Configuration Considerations

One of the big configuration decisions is whether to use one large machine or distribute your DW/BI system across multiple servers. A small company can run a DW/BI system on a single server that runs the relational database, Integration Services, Analysis Services, and Reporting Services with IIS. A large enterprise's DW/BI system will be broken apart over multiple servers, some clustered. How can you possibly determine what configuration is going to be most appropriate for your workload? It helps to break the question down into the major system components and configuration options. These include memory, processors, storage, monolithic or distributed systems, and high availability systems.

4.2.1. How Much Memory?

All of the SQL Server DW/BI components love physical memory. The relational database uses memory at query time to resolve the DW/BI style of query, and during ETL processing for index restructuring. Analysis Services uses memory for resolving queries and performing calculations, for caching result sets, and for managing user session information. During processing, Analysis Services uses memory to compute aggregations, data mining models, and any stored calculations. The whole point of Integration Services' data flow pipeline is to avoid temporarily writing data to disk during the ETL process. Depending on your package design, you may need several times as much memory as your largest incremental processing set. Reporting Services ...