Managing SQL Server databases

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

iBase supports SQL Server's Transparent Data Encryption (TDE) feature. TDE ensures data is encrypted at rest; that is the physical files on the disk where SQL Server stores your data are encrypted. Data in the server's memory or on the network is not encrypted. You can add a layer to protect data in transit over the network with Transport Layer Security (TLS). iBase does not support the Always Encrypted feature, introduced in SQL Server 2016.

For detailed information about configuring the security of the overall system, see Managing access to data and functions.

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.

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: 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.

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

See Setting Up iBase Database Replication in SQL Server for details of how to set up SQL Server replication for iBase databases.

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.