Maintenance
After a database has been created and all objects and data have been added and are in use, there will be times when maintenance must be performed. For example, it is important to back up the database regularly. You may also need to create some new indexes to improve performance. These issues should be taken into consideration when you design the database to minimize the effect on users, the time taken to perform the task, and the effort involved.
Maintenance design guidelines include:
- Designing the database to be as small as possible and to exclude redundant information.
Normalizing your database can help you achieve this. For example, reducing the size of the database can help reduce the time taken to back up or, more importantly, restore a database. This is especially important during a restore operation because the database is unavailable while it is being restored.
- Designing partitioned tables rather than a single table, if the table will contain a large number of rows.
For example, a table containing every credit card transaction received by a bank could be split into multiple tables, with each table holding data for a single month. This can ease index maintenance if new indexes would otherwise have to be added to improve query performance. It may be necessary to create the index only on data from the last three months because older data is no longer referenced. The larger the table, the longer it takes to create new indexes.
Microsoft® SQL Server™ 2000 provides the Database Maintenance Plan Wizard for automating many of these tasks, thereby reducing or removing the work involved in database maintenance.