Chapter 13 -- Maintaining a SQL Server Data Warehouse

Chapter 13

About This Chapter

This chapter describes various activities and methods involved in maintaining the data in and the physical structure of a SQL Server data warehouse. You should develop a specific maintenance plan; SQL Server provides the Maintenance Plan wizard as a good starting point. Data in the data warehouse must be updated and synchronized regularly. OLAP Services provides three methods for updating your OLAP cubes.

In addition to using standard backup and restore procedures to back up and restore data in the SQL Server data warehouse databases, you need to implement a separate set of backup routines for your OLAP Services databases. The Archive and Restore Add-in enables you to back up, archive, and restore OLAP Services databases.

OLAP Services stores the metadata about your cubes in a repository. The OLAP Services Repository is stored in a Jet database by default, but you can migrate it to SQL Server for greater fault tolerance and manageability.

Before You Begin

To complete the lessons in this chapter, you must

• Have installed SQL Server 7.0 and OLAP Services.
• Be able to log in to SQL Server as an administrator.



• Have installed the Exercise files from the Supplemental Course Materials CD-ROM to your hard disk drive. See the "Getting Started" section in "About This Book" for installation instructions.
• Have completed the exercises in Chapter 8, "Advanced DTS," to create and populate the Northwind_Mart data warehouse database. If you have not created the Northwind_Mart database, you can execute the C:\SQLDW\Exercise\Ch08\MakeMart.BAT batch file to create and populate the database. You cannot perform the exercises in this chapter if you have not created and populated the Northwind_Mart database.
• Have completed the exercises in Chapter 9, "Microsoft SQL Server OLAP Services," to create and populate the Northwind_DSS OLAP Services database and the Sales cube. If you have not created the Northwind_DSS database and the Sales cube, you can execute the C:\SQLDW\Exercise\Ch09\MakeMDdb.BAT batch file to create and populate the database and the cube. You cannot perform the exercises in this chapter if you have not created and populated the Northwind_DSS database and the Sales cube.
• Have installed the Archive and Restore Add-in in OLAP Manager. See "About This Book" for instructions on how to install the Add-in.