Updating the schema of a replicated database

Changing the schema of an iBase database requires the SQL Server administrator to stop replication for all sites while you apply an updated database template to the database at each site. Before replication is stopped, you need to prepare the database by reviewing all existing conflicts and then take the database offline. After the schema changes are applied at each site, the SQL Server administrator must reconfigure replication.

Before you begin

You need to prepare iBase so that replication can be stopped by the SQL Server administrator. This needs to be done in the publication database as the Conflict Viewer is only available at the Publisher:

  1. Log on as a database or system administrator and open the publication database.

  2. Using the File Manager, distribute the database template that contains the revised schema to the other sites. The template must remain loaded in the database.

  3. Ask any users that are using the database to log off.

    Note: If you use iBase Scheduler, disable the Scheduler service, after this point, you must not allow any imports to run.

  4. Check that all database connections are closed. For example, if you can log on to the remote servers, try to open the database in iBase Designer. If you can open iBase Designer, then you have exclusive access to the database at that site. If necessary, ask the SQL Server administrator to log out the remaining users.

  5. Review the current conflicts in the Conflict Viewer. If you change any of the records that are involved in the conflicts make a note of the last record that you change and the nature of the change.

  6. Check that any records modified as a result of using the Conflict Viewer are replicated to all the subscription databases.

  7. Ask the SQL Server administrator to disable replication.

  8. When the SQL Server administrator informs you that replication is disabled, open the publication database and run the Status report to confirm this. If replication is disabled, it reports Publication not found.

You are now ready to apply the schema change.

Warning: After replication is disabled, you must not make any changes to the data in any of the databases. The changes will not be replicated to the other sites when replication is reconfigured in SQL Server.

About this task

Once the SQL Server administrator informs you that replication is disabled, you can apply the new schema to each database by using Update Database Schema at each site. When you run this command, iBase closes and then reopens the database to gain exclusive access to it.

You must apply the same schema to all the databases. Update Database Schema displays the differences between the schema in the currently loaded template and the schema of the current database. The displayed additions, modifications, and deletions are applied to the current database.

Procedure

To apply a schema change at each site:

  1. Ensure that you have a backup of the database.

  2. Select Tools > Replication > Update Database Schema. iBase closes and reopens the database before displaying Update Database Schema. Update Database Schema is not displayed if you are a member of a Data Access Control group that denies access to any tables or fields in the database.

    Note: If you are warned that there is no database template, load the correct template by using the File Manager. However, do not load any other files into the database as all the files listed in the File Manager will be overwritten once replication is reconfigured.

  3. If required, save a list of schema changes in a format that is useful for the SQL Server administrator at your site. You can print this file later.

  4. Click Update to apply the changes and then, once this is finished, click OK to reopen the database.

  5. At each site, check that the template has been applied successfully. Once all subscriber sites have applied the schema change, notify the SQL Server administrator that replication can be reconfigured. When the SQL Server administrator informs you that replication is running again for all the sites:

  6. Verify that replication is running, for example, by running a status report. You may want to test that replication is running correctly.

  7. Open the publication database and make the database available again to other users, select Tools > Replication > Bring Online . After a short interval, the status is replicated to the Subscribers.

  8. Notify users that they can start work again.

  9. If you use iBase Scheduler, restart the Scheduler service.