Routine maintenance
There are several areas in iBase that require routine maintenance to ensure that your database continues to run correctly. Where possible, you can use tools that are provided in iBase Designer to run maintenance tasks.
The commands for routine database maintenance are available from the Tools menu in iBase Designer:
- Maintaining database tables and indexes
- All databases and security files operate more slowly as deletions and changes increase the
fragmentation of the data.
For Microsoft™ Access databases and security files, use the relevant option.
For more information about maintaining tables and indexes in SQL Server databases, see Performance Tuning in iBase Designer. - Maintaining search indexes
- Depending on the type of search, the method of maintaining the search index varies:
- For Search 360, ensure that:
- The Index Service is scheduled to run regularly.
- The IBaseIndexDB database and Searching Config.xml configuration file are included in your backup schedule.
- The transaction log is monitored, and cleared when it becomes too large.
- For Word Search indexes, run each time that you want to update the index.
- For Full-Text Search indexes, you can use to set up ongoing updates, either with a regular schedule or in response to changes in the database content. On a less regular basis, you might want to respond to user comments or new types of recorded data by updating the lists of excluded words or synonyms.
For more information, see Setting Up Search.
- For Search 360, ensure that:
- Check for responsiveness and integrity of the database.
- If users report slow performance or recurring errors in normal operation, it might indicate
fragmented disk files or some kind of corruption.
In iBase Designer, you can use the commands: Repair/Compact Database File, Schema Integrity Check, and Link Integrity Check. There might also be causes external to the database system, such as other processes that run on the server or client computers or poor network connections.
For more information, see Checking a database. - Managing databases that use Soft Delete
- In databases using soft deletion of records, purge or restore records as required. For more information, see Batch delete.
- Managing databases that use cases
- In databases that use cases, add new cases, give and revoke access to cases, and close old cases as required. For more information, see iBase cases.
Monitoring iBase usage
- Audit logs
- Use the external iBase Audit Viewer to monitor usage and identify any repeated events such as failed logons or repeated editing or analysis activities that might indicate user difficulties.
- Pick lists
- You might find that users are frequently typing to supply alternatives to entries suggested in a pick list. You can add these entries to the pick list if required.
- Datasheets
- Datasheets provide alternative ways of creating or viewing records. Their effectiveness might
need monitoring. For example, you might find that analysts want to view an entity in association
with selected fields from a particular type of link and linked entity, or that data entry might be
made faster by reordering and regrouping the fields of information.
Select to start editing or creating datasheets.
Reviewing database design, statistics, and security
At any time, you can use iBase Designer to view, or change, the database properties that are chosen when you created the database, and view data statistics and a database design report. With default access control, all users of iBase can view, but not change, database properties, database statistics, and a database design and statistics report. Select the relevant command from .
The security design report can have several forms, but always lists security groups, users, and their consequent permissions or restrictions. You can choose to include user information if required.
The security design report presents all the information held in the security file to which you are logged on. The report does this first by group, listing the group's properties (if any) and user membership; then by user, listing the accumulated permissions of the user, possibly gained by membership of several groups, and the groups of which the user is a member.
Routine maintenance on the database servers
You need to maintain adequate free space on disk for databases, search indexes, audit logs, and any linked documents. This is largely a matter of using tools supplied with Windows™ to monitor both the free space and the size of the files that are growing most quickly to reduce that free space.
The strategy that you adopt for databases might vary from moving old data to archives with iBase batch export, batch delete, or creating new databases to hold current data for each year or other time period. For audit logs, the external iBase Audit Viewer provides a way to view, archive, and delete old audit records.
You need to maintain adequate backups of the database, security file, and audit logs. You should schedule backups for a time when no users are using the database. See Backing Up iBase Databases.
Database backup procedures depend upon the type of database, Microsoft Access or SQL Server. Back up each security file frequently, as a complete file. Back up audit logs using the external iBase Audit Viewer to identify data for archiving. For further details, see the Audit Viewer help.
Routine maintenance in SQL Server
For large SQL Server databases, disk operations have a significant effect on the performance of the database. To reduce the amount of data that is read from disk during queries, iBase applies indexes to the data. Over time as data is added to and deleted from the database the indexes become fragmented and larger than they need to be. This reduces performance because more data blocks are read into memory to process a query. Eventually, without corrective action, the result is queries that run many times slower than in a newly indexed database.
- For databases that use legacy indexing or that are tuned in SQL Server, this is done by your SQL Server administrator.
- For databases that use indexes that are optimized in iBase (and that are not tuned in SQL Server), this can be done in the Performance Tuning dialog in iBase Designer. See Performance Tuning in iBase Designer for details.
The indexes should be rebuilt regularly, at intervals that are determined by your SQL Server administrator who is able to measure the fragmentation of the indexes using tools in Enterprise Manager or Management Studio. For example, a database that is updated with imports that use the Bulk Import method might require reindexing after each bulk import.
Each rebuild takes some time and should be scheduled to take place when the database is not in use.