Changing the schema of a replicated database

Schema changes cannot be replicated. To change the schema of a replicated database, the SQL Server administrator must disable replication for all sites while you update the database template at each site. After you apply the changes at each site, the SQL Server administrator must to reconfigure replication. For this procedure, all Subscribers must be connected to the Publisher.

About this task

The different stages of the operation are:

Preparing a new database template with the wanted schema (you need to work on a test database). When the new template is ready, you load the new template into your production database by using the File Manager - replication automatically distributes it to all sites.
Preparing the database so that replication can be disabled. This involves taking the database offline (to prevent further conflicts from occurring) and then reviewing all existing conflicts. After this is done the SQL Server administrator disables replication by deleting the publication and its subscriptions.
Applying the new schema at all sites. Only changes to entity types, link types, fields, standard fields, code lists, semantic types, datasheets, and common folder objects are applied.
After the new schema is applied, the SQL Server administrator reconfigures replication.

Note: Although the changes to the database schema are not discarded, they cannot be replicated. Changing the schema in this way, can also prevent you from using File Manager and Update Database Schema to update the schema as described in the following information.

What is a schema change?

Any of the following changes to the database design constitutes a schema change (and are not replicated):

Adding, modifying, or deleting entity types, link types, fields or standard fields
Adding, renaming, or deleting pick lists, icon lists, or Security Classification Code lists
Note: Adding or editing the description of the list is a schema change. Changing the content of these lists is not a schema change, and any changes you make to the contents are replicated.
Adding, modifying, or deleting common folder objects
Adding, modifying, or deleting datasheets
Adding semantic types (whether manually or by loading a custom semantic type library) and assigning them

Do not delete anything from a database schema once the database is live.

Procedure

To change the database schema, you can work at any one of the sites involved in replication:
1. Create a database template to capture the current schema.
2. Using the template, create a temporary database in which to develop and test the modified schema. The database can be either an Access or an SQL Server database, whichever is most convenient.
3. Use the temporary database to develop the new database schema, and then test it to check that it works as intended.
4. Create a database template to capture the updated schema. You should test the database template by applying it to a parallel database.
Note: At this stage, you might want to plan what changes (if any) are required to other iBase object such as report definitions or labeling schemes.
Use the File Manager to replicate the new template to all the sites:
1. Log on as a database administrator, open one of the replicated databases, and load the database template into the database using the File Manager.
  See Replicating Non-Database Files for details.
  The database template is replicated to all sites.
2. Check that all Subscribers received the new database template.
  
  Note: Do not delete the database template from the File Manager dialog.
3. Optional: At each site, save the new template to the WorkgroupTemplates folder of the local iBase installation.
After all sites receive the new database template, you need to prepare iBase so that replication can be disabled by the SQL Server administrator. This needs to be done in the publication database:
1. If you use iBase Scheduler, disable the Scheduler service to prevent any imports from running. Disabling rather than stopping the service prevents the service from restarting if the server is rebooted.
2. Make sure that all Subscribers are connected to the Publisher.
3. Log on as a database administrator and open the publication database.
4. Take the database offline: in iBase, select Tools > Replication Take Offline.
  See Shutting Down the Databases for details.
  
  This is replicated after a short interval, and the message is displayed to all users actively working in iBase or trying to open one of the databases.
5. Check that everyone closed the databases. For example, if you can log on to the remote servers, try to open each of the databases in iBase Designer. If necessary, ask the SQL Server administrator to log out the remaining users on all sites.
6. Review the current conflicts in the Conflict Viewer: in iBase, from the Tools menu, select Replication Conflict Viewer. If you change any of the records involved in the conflicts make a note of the last record that you change and the nature of the change. (This helps you to check later that the modified records reached the subscriber sites.)
  
  Any changes that you make in the Conflict Viewer are replicated to all subscriber sites.
7. Check that any records modified as a result of using the Conflict Viewer reached all subscriber sites.
8. Ask the SQL Server administrator to disable replication.
  
  Warning: After replication is disabled, you must not make any changes to the data in any of the databases. The changes are not replicated to the other sites after replication is reconfigured.
After the SQL Server administrator informs you that replication is disabled, you can apply the new schema to the databases. Before you start, ensure that you have a backup of the database and that replication is disabled.
- If you are working in the publication database, run the Status report to check that replication is disabled: in iBase, from the Tools menu, select Replication Status Report. It reports Publication not found.
- If you are working in a subscription database, open the database in iBase Designer. If replication is still running, you see the message:
```
*** WARNING *** This is a replicated database. You must not change the database schema.
```
To apply the schema change at each site:
1. In iBase, log on as a database administrator, and select Tools > Replication Update Database Schema.
  
  Note: You cannot display this dialog if you are a member of a Data Access Control group that denies access to any tables or fields in the database.
2. Click OK to close the database.
  If you are warned that there is no database template, you can still load it manually. However, do not load any other files into the database as all the files listed in the File Manager can be overwritten after replication is reconfigured.
3. Optional: Save a list of schema changes in a format that is useful for the SQL Server administrator: in the Update Database Schema dialog, click . 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. Check that the template is applied successfully.
After all subscriber sites apply the schema change, notify the SQL Server administrator that replication can be reconfigured.
When the SQL Server administrator informs you that replication is reconfigured:
1. Verify that replication is running: for example, open the publication database in iBase, and from the Tools menu, select Replication Status Report. The message Publication is OK is displayed.
  
  You might also want to test that replication is running correctly. See Testing Replicated Databases for details.
2. In iBase, log on as a database administrator, open the publication database and make the database available again to other users: from the menu, select Tools > Replication Bring Online.
  
  After a short interval, the status is replicated to the subscriber sites.
3. Notify users that they can log on again.
4. If you use iBase Scheduler, restart the Scheduler service.
  
  Warning: After replication is configured, you should not open the database in iBase Designer unless necessary (unless you are a security administrator).