Chapter 11. Very Large Data Warehouses

Very large databases (VLDBs) magnify inefficiencies that are unnoticeable in smaller databases into show-stopping issues. Common issues associated with very large amounts of data include problems such as speed of importing data, lengthy backup process, excessive unavailability due to long maintenance processes, lengthy processes to retire unneeded historical data, and poor query performance.

Another issue often associated with large databases is a high rate of growth. Large amounts of new data arriving need high-performance extraction, transformation, and loading (ETL) processing.

Even if your database is not multi-terabytes in size, you may be experiencing some of these problems as your database grows beyond expectations. The term very large is relative. You might have limited hardware, which can make some processes run more slowly even on a few hundred gigabytes of data. You might have a very small window of time to perform some maintenance operations. In these circumstances, you might be willing to give up some simplicity of your original design in return for more optimized operations.

In this chapter, we look at techniques available for handling very large volumes of data in the SQL Server 2005 relational engine and Analysis Services. We also cover how to work with Integration Services to handle very high rates of data flow into the data warehouse.