Managing SQL Server databases

iBase provides the capabilities to store data in Microsoft™ SQL Server databases and Microsoft Access databases. Microsoft Access should be used as the supporting database only if the number of simultaneous users is five or less. When a database of more than 200 Mb is accessed by a number of users simultaneously then consideration should be given to using SQL Server.

Upgrading an iBase database to SQL Server

You can use iBase Designer to convert a Microsoft Access database to SQL Server format. The upsize process creates an SQL Server database and an .idb file that contains the connection details. For details of this process, see Upsizing a Database to SQL Server.

Managing the security of the data in an SQL Server database

For detailed information about configuring the security of the overall system, see the Administration Center document Managing Access Control, which provides detailed guidelines on how to control access to iBase.

Populating the SQL Server database

If you need to import very large quantities of data, then you might want to consider using bulk import or XML import. Bulk import makes use of the SQL Server BULK INSERT statement and requires the database and server to be configured before it can be used. For further information, see Overview of Bulk Import.

Optionally, iBase can load data that is extracted and structured from source documents using Text Chart. For further information, see the Administration Center document Using iBase with Text Chart.

Attention: You cannot use the general SQL Server tools to populate iBase SQL Server databases. The iBase application must have complete control of the data in the database to ensure the integrity of the entities and the links between them. Any data that is not entered or imported by iBase tools can render the whole database corrupted.

Keeping data safe and available (backup)

This is probably the most complex area of managing a database installation, and iBase with SQL Server is no different. SQL Server provides tools for completing the backups and automating them, although your SQL Server administrator might use other backup tools if the right files are backed up at suitable intervals.

With your SQL Server administrator, you must to decide on your backup regime. This can depend on how the iBase SQL Server databases are populated: for example, whether the database is populated by users entering data continuously or by users importing large sets of data. For further information, see Backing Up iBase Databases.

Note: Perform database backups at a time when no users are using the database. This is because some iBase operations can take place over a relatively long time and affect multiple database records. Examples of such operations are data imports, batch edit, batch delete, merge, or deletion of entities with many links. If the backup was performed during such an operation and the database is subsequently restored from the backup the restore operation restores data on which work was in progress at the time the backup was taken and is therefore potentially in an incomplete state. It is safest if backups are completed when no users are performing operations on the database.

Modifying the database schema

Your SQL Server administrator cannot modify the schema of an iBase SQL Server database using SQL. The schema is part of the structure of iBase, and must remain unchanged to ensure data integrity and the success of future upgrades. The only way that you can modify the schema is to use iBase Designer.
Note: It is possible for an SQL Server administrator to modify the indexes of an iBase database to improve performance in areas such as querying although there is a tool for doing this in iBase Designer— see Performance Tuning in iBase Designer for details. Completing this step manually needs careful planning, and your SQL Server administrator should keep detailed notes and take SQL scripts of the changes to default indexing. Completing this step manually prevents the use of the iBase Designer Performance Tuning wizard.
Note: Before you modify the indexes, your SQL Server administrator must to stop the Microsoft Search service if it is used to continuously update the Full-Text Search indexes in iBase. Other services, such as alerting, are stopped automatically when you open the database in iBase Designer.

Performance tuning in SQL Server

The performance of an iBase SQL Server database can be maintained by regular reindexing in SQL Server. A decline in performance might become apparent after the database grows larger than, possibly, 10 – 15 GB, and is most noticeable when you run iBase queries. If you are using a database upgraded from iBase 4, then you might be able to improve the performance of queries by optimizing the database indexes. A tool for doing this is available in iBase Designer— see Performance Tuning for details.

If you are already using query-optimized indexes (which is the case for databases created or upsized in iBase 5) and query performance is still poor, you need to discuss the problem with your SQL Server administrator. Setting aside issues with hardware and network infrastructure, the decline in performance might occur for various reasons in Microsoft SQL Server:
  • Frequent data imports caused the data and indexes to become fragmented
  • Databases that are set to grow/shrink automatically on the same disk became fragmented
  • Inserting, updating, or deleting large amounts of data caused the SQL Server database statistics to become out-of-date
There are a number of steps that an SQL Server administrator can take to address these problems:
  • Data and index fragmentation can be addressed by rebuilding or defragmenting the indexes on the database tables. An SQL Server administrator can do this while the database is online but, for the best results, it is preferable to first take the database offline.
  • Operating system fragmentation can be resolved by defragmenting the disk files. This can be done by a server administrator rather than by an SQL Server administrator. It also requires the database to be taken offline so that the files can be moved around the physical disk.
  • If automatic statistics updating is disabled, an SQL Server administrator can update them manually.   

Effect of auditing on performance

Standard auditing of updates and deletions has a low impact on performance. However, the read auditing that can be configured as an option for iBase SQL Server databases does have an impact. The design of this auditing is such that only records, which have been displayed, charted, or reported are audited. This means that activities such as finding and querying do not run noticeably slower. Activities that result in a revealing a record, such as charting, can take more time to complete. If you intend to use read auditing extensively, it is possible to configure the Audit log database to write to files on disks with fast write performance (see Server machines for details).

Read audit places a higher load on the network and so network performance is more important when using this option. The read audit logs grow relatively quickly and should be archived regularly.

SQL Server Replication and iBase

For details of how to replicate iBase databases, see the Administration Center document Setting Up iBase database replication. iBase database replication is a separately licensed feature.

For more information on hardware requirements, see SQL Server Clients, Servers and Networks.

You can use iBase installation to work with data in both SQL Server and Microsoft Access database formats. This allows you to work with the scale of data appropriate to your analysis. iBase automatically recognizes the type of database and you can switch between them within an iBase session.