Database upgrades

Database upgrades changes the structure of your database to add new features. Database upgrades should only be carried out after backing up your system to allow you to roll back your upgrade if required.

Backing up the system

You should always back up all iBase files prior to any maintenance work, to create a restore point.

To identify the iBase files:

Identifying all iBase databases

You can obtain the location and names of databases on each machine through the MRU List Manager accessed from the Tools menu in either iBase or iBase Designer. The list of files you obtain from the MRU List Manager will provide the paths and names of the databases that have been opened by the currently logged-in user – even if the database no longer exists. It is advisable to ask the user of the machine to provide this list for you prior to the upgrade.

Identifying all iBase database files

Before you upgrade an iBase database, you must back up every file it uses. Locating the iBase security (.ids) and database (.idb) files will reveal most of the others. The iBase security file is usually located in the same folder as the databases it secures.

The files that you should back up are listed in Database file types.

Determining the types of database in use

The iBase security and database files will help you identify the locations of any SQL Server databases that iBase uses. To determine whether the security and database files are SQL Server or Access:

In an iBase client (not iBase Designer), log in as a system administrator, and open each database.
Select File > Security File Properties, and File > Database Properties in turn.

In the case of SQL Server security files and databases, the properties dialogs will display the database name and the SQL Server name. In general:

An iBase database name will not contain any spaces, and will always contain at least one underscore character.
An iBase database will also have an associated audit log database that has the same name as the database, with a suffix of _Log.
An iBase security file database is suffixed with _Sec.

You must make a note of the names of the SQL Server and the iBase databases, ready for the next part of the process.

When you have a full list of all the files and databases that are required, you can start the backup process. Ensure that you make copies of all the files, and that they are stored in a safe location. You will need both network and database admin permissions in order to back up your system successfully.

Note: When making any changes to the database, you should ensure that database replication has been stopped by the SQL Server DBA before the changes are carried out. Replication of the audit log is optional, but if you have chosen to replicate it, the SQL Server DBA must stop replication on this database too.

Before backing up the database, ensure that the Scheduler service on the machine that runs scheduled tasks is not performing an import or export in a database that you are in the process of updating. Then, place the service in the ‘stopped’ state to prevent future imports or exports from running.

Note: If the Scheduler is in the process of running an import or export, wait for it to complete before stopping the service.

For information on how to stop a Windows service, refer to the Microsoft support pages.

Note: If multiple iBase databases are using the Scheduler, an alternative to stopping the service is to deactivate any Scheduler triggers that operate over the databases you are updating. This will ensure that scheduled imports for the other databases continue unaffected.

To back up the SQL Server database:

Log in to the system with database administrator rights.
Verify that there are no users accessing the database.
Set the user access option on the iBase SQL Server databases to single user. Provided the DBA does not exit the database, only the DBA will have access to the databases, preventing changes from being made during the backup.
Perform the backups (including any Analysis Services databases and Audit Log databases for this database), and then take the databases offline.
Note: Changing the user access option to single user will not prevent users from accessing the database altogether after the SQL Server DBA has made the backup and exited the database. However, it does restrict the number of users and the amount of change that may occur if a user does access the database. If you are upgrading multiple databases, you must perform steps 3 and 4 sequentially for each database in turn. This will prevent other iBase users from opening the database and locking you out.

To back up the files:

Log in to the system with network administrator rights.
Identify the files to be backed up from the list that you prepared earlier.
Back up the files to a safe location.

Between backing up and upgrading the databases, you must not allow any users to access iBase security files or databases. Locking users out of the database after the backup ensures that no data loss occurs in the unlikely event that you encounter problems during the upgrade.

Note: Ensure that the backup has been moved to a secure location before proceeding, and restore the backup to a test environment to ensure all key files have been recorded.

Upgrading the security files and databases

Usually, you will upgrade all security files and databases, but there may be occasions when you need to keep some databases in an earlier iBase format. For example, you might share these databases (or charts created from them) with other agencies that are not in a position to upgrade.

If there is a reason why you cannot upgrade a particular database to iBase, you can still selectively upgrade other databases. However, you should be aware that when you upgrade a security file, you must also upgrade all databases secured by that security file. To upgrade a subset of databases that are secured by a single security file, you must copy the security file and move the database that you want to convert to a new location.

Note: Copying a security file that is in SQL Server format requires that you also copy the SQL Server component of the security file to a new instance of SQL Server.

Finally, to perform the upgrade of the iBase database and its security file:

If you have a SQL Server security file, ask the database administrator to bring the security database online.
Open the security file in iBase Designer; this will initiate the upgrade.
Ask the database administrator to bring the log file and database online.
Open the database in iBase Designer.
Select the Upgrade option when prompted; this will initiate the upgrade.
Ask the database administrator to change the upgraded database to multi-user mode, and allow the users back in to the database.

During this process, iBase Designer displays a series of warning and status messages that you must acknowledge in order to continue. When the upgrade is complete, you can only roll back the process by reverting to your backups of the connection files and SQL Server databases.

Additional actions for users of iBase Scheduler: If you are planning to perform a staggered upgrade, enable iBase Scheduler to perform its imports and exports by either starting the Scheduler service, or reactivating the Scheduler triggers using the iBase Scheduler Configuration tool.
Additional actions for replicated databases: If you are planning to perform a staggered upgrade, ask your SQL Server DBA to reconfigure replication between the servers before allowing any users to access the database again.