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

15.2. System Management

Most of this chapter focuses on the back-room requirements for managing your DW/BI system in production. Although we've separated these operational issues into this chapter, you need to think ahead during design and development to ensure you build a maintainable system.

There are several components of the back-room system management:

• Executing and monitoring the ETL system

• Monitoring resources and usage

• Managing data growth and disk space

• Performance tuning

• Managing partitioning

• Backup and recovery

• Generating statistics for the BI portal

The more automated you can make your systems management, the better. At the very least, automate backups and launching the ETL packages. SQL Server provides enough tools that the basics are easy, and there's no excuse for not implementing some system automation.

Unlike many issues where we've talked about how small teams might cut corners, organizations of any size benefit from system automation. Indeed, the smallest organizations are perhaps least equipped to apply human resources to a problem that can be automated. It's hard to imagine how a DW/BI team of one to three people could possibly operate without significant automation.

The ideal management system requires no human intervention except for the occasional troubleshooting. Such a system automatically adds and drops partitions, checks for disk space, reports on performance problems or unusual usage, and corrects the vast majority of data oddities during the ETL process. ...