Lesson 1: Developing a Maintenance Plan

Maintaining data in a data warehouse is an ongoing effort that requires careful planning in order to provide continuous access to data. You need to identify routine maintenance tasks and implement a plan for performing these tasks regularly. SQL Server provides tools for automating many of these tasks.

After this lesson, you will be able to:

• Develop a maintenance plan
• Automate administrative tasks

Estimated lesson time: 20 minutes

Figure 13.1 shows the data stores and databases that are involved in a data warehouse. You need to plan for the ongoing maintenance of each of these parts in order to keep your data warehouse healthy.

Figure 13.1 Data stores and databases of a data warehouse

Identifying Routine Maintenance Tasks

The first step in creating a maintenance and recovery plan for your data warehouse is to determine how mission-critical is each part of your data warehouse. This information will be combined with cost and downtime information to decide between multiple maintenance and recovery plans. Maintaining the infrastructure of a data warehouse is similar to maintaining the systems and databases that you already know. To make the task easier, you can identify and set up the core maintenance tasks that are necessary to ensure that your data warehouse performs well. These tasks include regular backups and checks for data inconsistencies.

Consider all of the following parts of the data warehouse when you identify the routine maintenance tasks.

OLTP Data

This element is the point at which database maintenance starts. It is critical that data consistency checks and frequent database and log backups are maintained for all systems that will be providing data to our data warehouse.

Temporary Data Staging Area

Make sure that you have backup copies of all of the DTS packages, objects, or scripts that you use to transform and scrub data. It is good practice to make new backups whenever these items change and to add them to a scheduled backup to cover the rare times when manual backups are not created.

Operational Data

This is one of the most critical data warehousing elements. Operational data requires frequent valid backups. If the data warehouse data is lost, it can take a long time to gather the data from the OLTP or archive and retransform the data.

Data Marts

If the data warehouse is created first, it is typically easy to repopulate the data mart with data from the data warehouse. If the data mart is developed first, it is important to maintain frequent, valid backups.

OLAP Data

OLAP data is derived from the data warehouse or data mart. Comparing the time required to perform a database restore with the time required to rebuild OLAP data will typically show how important it is to keep current backups of your OLAP data.

Performing Maintenance Tasks Periodically

In general, you should review and perform these maintenance tasks periodically:

• Back up all system and user databases.
• Maintain and secure system logs.

Keep a record of all service packs that you install for both Microsoft Windows NT Server and Microsoft SQL Server. Keep records of the network libraries, the security mode, and the system administrator password.

• Maintain scripts for re-establishing minimal capability quickly in case of a serious failure.
• Perform regular database and transaction log backups to minimize data loss.
• Practice system and data recovery steps ahead of time on another server, and modify the steps as necessary to adapt to your environment.
• Create a data exposure analysis that defines downtimes for recovery and any potential data loss from each possible system failure. Review this document with the data owners to verify that it is acceptable.

Automating Administrative Tasks

You can schedule many administrative tasks to run automatically. By automating recurring administrative tasks and responses to server events, you can free time to perform administrative tasks that require creativity and lack predictable or programmable responses.

Identifying Maintenance Tasks to Automate

You can schedule the following maintenance tasks to run automatically:

• Reorganizing the data and index pages by rebuilding indexes with a new fill factor.

This ensures that database pages contain an equally distributed amount of data and free space. Evenly distributing the free space effectively reserves space for new data, thereby accelerating data inserts. Eliminating free space may allow a read-only database to use cache more efficiently.

• Reducing the size of data files by removing empty database pages. Reducing file size utilizes disk space more efficiently.
• Updating index statistics to ensure that the query optimizer has current information regarding the distribution of data values in the tables.

Index statistics provide information about the data that is stored in the database for the query optimizer so that it can determine the best way to access data. Although SQL Server periodically updates index statistics automatically, this option can force statistics to update immediately. This is particularly useful if a large amount of data has been added or changed.

• Performing internal consistency checks of the data and data pages within the database to ensure that a system or software problem has not damaged data.
• Backing up the database and transaction log files.

You can retain database and log backups for specified periods. This allows you to create a history of backups that you can use when you need to restore the database to a time before the last database backup.

Using the Database Maintenance Plan Wizard

SQL Server provides the Database Maintenance Plan wizard for automating many of these tasks, thereby reducing the work involved in database maintenance. With this wizard, you can run database integrity checks, update database statistics, and perform database backups.

You can specify that the results of maintenance tasks are written as a report to a text file, a Hypertext Markup Language (HTML) file, or the sysdbmaintplan_history tables in the msdb database. You also can send the report by e-mail to an operator.

Using Data Transformation Services

You can use Data Transformation Services (DTS) to automate additional administration tasks that are related to data movement. When you use DTS to automate administration tasks, you must

• Establish which administrative responsibilities or server events occur regularly and can be administered programmatically.
• Define a set of jobs, operators, and alerts.

To define jobs, operators, and alerts, you can use SQL Server Enterprise Manager, Transact-SQL scripts, or SQL-DMO objects.

• Run SQLServerAgent service with necessary security permissions.

Exercise: Creating a Database Maintenance Plan

In this exercise, you will use the Database Maintenance Plan wizard to create a database maintenance plan for the Northwind_Mart database.

To create a database maintenance plan that consists of multiple jobs

In this procedure, you will create a plan by using the Database Maintenance Plan wizard.

1. Open SQL Server Enterprise Manager and expand your server.
2. Expand the Management folder and confirm that SQL Server Agent is started. (Hint: The SQL Server Agent is started if there is a circle containing a green arrowhead over the agent icon. The SQL Server Agent is stopped if there is a circle containing a red square over the agent icon. To start the agent, right-click the agent and select Start on the menu.)
3. On the Tools menu, click Wizards.
4. Expand Management, and then double-click the Database Maintenance Plan wizard.
5. Use the information in the following table to create a comprehensive maintenance plan for the Northwind_Mart database. Accept the defaults for any items that are not listed.

Option	Value	Schedule
Update statistics used by query optimizer	10%	Every three months on the first day of the month at 1:00 A.M.
Check database integrity	Yes Include indexes	The first Saturday of each month at 11:00 A.M.
Back up the database as part of the maintenance plan	Yes	The first Saturday of each month at 11:00 A.M.
Remove files older than (for the database backup)	Three months	NA
Back up the transaction log as part of the maintenance plan	Yes	Every week on Sunday at 11:00 P.M.
Remove files older than (for the transaction log backup)	Four weeks	NA
Write report to a text file in directory	Yes C:\MSSQL7\LOG	NA
Write history to the msdb.dbo.sysdbmaintplan _history tableon this server	Yes Limit rows in the table to 1,000 rows for this plan	NA
Plan name	Nwind_Mart_Maintenance	NA

6. Click Finish.

To work with a database maintenance plan

In this procedure, you will view the database maintenance plan that you created, and then execute a job and review its history.

1. In the console tree, expand Management, and then click Database Maintenance Plans.
2. In the details pane, right-click Nwind_Mart_Maintenance, and then click Properties to review the properties of the database maintenance plan that you just created.
3. Close the Database Maintenance Plan dialog box.
4. In the console tree, expand SQL Server Agent, and then click Jobs. You will see four jobs with a Category of Database Maintenance. These jobs were created for your maintenance plan. As you review the syntax of each job, you can see how the wizard terms correspond to SQL commands.
5. In the details pane, right-click Integrity Checks Job for DB Maintenance Plan Nwind_Mart_Maintenance, and then click Start.

This starts the Integrity Checks job manually.

6. Right-click Integrity Checks Job for DB Maintenance Plan Nwind_Mart_Maintenance, and then click View Job History to review the job history and verify that the job executed successfully.

Lesson Summary

A number of routine tasks need to be performed to keep your data warehouse healthy. You must identify these tasks and implement a plan to ensure that they occur regularly and reliably. SQL Server makes it possible to automate most of these tasks. You need to check the reports and logs generated by the automated tasks to ensure that they are running correctly.