S 4.70 Monitoring a database

Initiation responsibility: Head of IT, IT Security Officer

Implementation responsibility: Administrator

To be able to guarantee the availability of the database and the integrity and confidentiality of its data, it is necessary to monitor the database regularly at appropriately defined monitoring intervals. The aspects to be taken into account in this case, which are explained briefly in the following, include the level of data fragmentation in the database, the current data volume stored, and changes to this amount in terms of the resources available (space actually used) as well as the load on the database, among other items.

Data fragmentation

The database is to be checked at regular intervals to determine if the data is too highly fragmented so that it is possible to plan and implement safeguards, for example the reorganisation of the database.

Storage space is usually managed in a database management system (DBMS) in the form of blocks of fixed size, which means the disk space can only be changed (usually increased) in increments of one block. Data records are stored by distributing the data between the minimum number of blocks. In principle, data is added using free blocks first, and new, additional blocks are only created when necessary. When data is deleted, the corresponding blocks are released and are then available for storing new data.

Over the course of time, numerous changes to the data causes the storage area to contain series of full and free blocks as well as a larger number of blocks not fully utilised. Furthermore, the data records become physically distributed all over the storage media. This fragmentation not only increases the memory requirements, but also slows down database operations, because it becomes necessary to search through a larger storage area for data records and free disk space.

If the level of fragmentation of the database exceeds a specified limit due to the reasons stated above, then is it necessary to reorganise the database. Database manufacturers and third-party providers offer administration and utility programs to support the defragmentation of the database.

Data volume and space actually used

To prevent data from becoming too highly fragmented or from being fragmented too quickly, some database management systems allow the users to reserve a certain number of contiguous blocks when creating a table by defining certain parameters. This leads to improvements regarding the space actually used for the same data volume.

The data volume stored in the database files and space actually used in the database files should be monitored regularly. During regular monitoring, it must be checked if the data volume and the space actually used are still within the specified limits. If the files grow more than expected, then storage bottlenecks can arise under some circumstances. Safeguards, for example the addition of more storage capacity, should be derived from these observations.

Example:

In an Oracle database, every table is assigned a fixed number of extents (Oracle terminology for a logical unit of a fixed size). The data of a table is stored in at least one extent. As soon as the capacity of an extent is exhausted, the DBMS automatically creates an additional extent. The following values can be defined when creating a table in Oracle:

If the free storage area available for a tablespace becomes too small through the creation of additional extents (see the example in T 2.39 Complexity of a DBMS), then a new tablespace must be added. The number of tablespaces can only be lowered by completely reorganising the database.

Load

In addition, the load placed on the database should be checked regularly, especially in terms of the upper limits set (see S 4.73 Specifying upper limits for selectable data records).

The information relevant for the monitoring of a specific database depends on the particular method of operation of the database, i.e. on which standard database software package is used. Accordingly, individual safeguards must be implemented that change the database configuration so that it meets the requirements in terms of its access speed, number of transactions to be performed etc.

The monitoring of a database can be performed automatically in many cases with the help of scripts. One prerequisite for the use of scripts is that the relevant information is provided by the database software used in a form that can be evaluated in a script.

Review questions:

© Federal Office for Information Security. All rights reserved.