Preparing existing databases

You must convert (upsize) iBase databases in Microsoft Access format to SQL Server before you can replicate them.

About this task

Upsizing creates an SQL Server database (leaving the .idb file in the database folder as a connection file for the SQL Server database). The connection file stores the details that are needed to connect to the server that holds the database. Before upsizing, review the database name because the logical name of the SQL Server database is derived from it. For information on file names, see Before creating any iBase databases.

You must set the database properties that are required for replication and then initialize the database. This database becomes the publication database. After you initialize a database, you made changes to the database tables that cannot be undone, and soft delete must always be used.

To perform these steps, you must log on as a database or system administrator.

Procedure

  1. Upsize the database to SQL Server:
    1. In iBase Designer, log on using the correct security connection file. You might need to upgrade the security file first. Do not open the database.
    2. Select Tools > Upsize Database to SQL Server
    3. Follow the instructions displayed by the Database Upsize Wizard and, when prompted, enter the named server for the publisher site and the correct security mode. Your SQL Server administrator tells you which server and security mode to use.
      Note: Do not select the Local option from the Server list.
    4. Click Finish to perform the upsize, and then Close once it is complete.
      Note: Back up the connection file (.idb file) to the database. If you lose the connection file, you are not able to open the publication database.
  2. Set the database properties required for replication, open the database in iBase Designer, then select File > Database Properties > Advanced.
    • Database identifier

      Each database in the replicated iBase system must have a unique site identifier, up to 5 characters in length, which is used by the Conflict Viewer and the audit log. The identifier is typically the same as the associated SQL Server security connection file for the site. For example, the site identifier for both the main publication database and its security file might be PUB.

    • Soft delete

      Soft delete must be turned on as this is required by the Conflict Viewer. You must not turn off soft delete after the database is initialized for replication as this prevents users from opening the database (even if replication is not currently configured in SQL Server).

    • Audit level

      Set the audit level to 4 or 5 if you want to log the results of conflict resolution. Although the audit level property is not replicated, it should be set the same at all sites involved in replication.

  3. Initialize the database for replication:
    1. In iBase Designer, log on as a database or system administrator using the correct security connection file and open the database.
    2. Select Tools > Feature Availability > Initialize Database for Replication, and then click Yes.
      This operation can take a long time on large databases.

      You do not need to do this step at the subscriber sites. They are initialized for replication when the SQL Server administrator creates a subscription to the publication.

  4. Create a database template.

    It is important to ensure that the design of the iBase database that you want to replicate is up-to-date and suitable for use by all sites before you continue. For details, see Updating the Database Design.

    If the database design is already up to date, then create a database template for use at the subscriber sites. The databases at all sites must use the same database design.


What to do next

StepDetails
Publication databaseRequest that your SQL Server administrator configures a publication for this database and, optionally, for the audit log database. The users of the publication database can continue to work in the database while replication is configured.
Subscription databasesSend the new database template to the subscriber sites. The subscription databases can then be created from this template. See Creating Subscription Databases.
Secure the databasesAsk your Windows administrator to restrict access to the iBase database folders and to any other folders used by iBase, for example to the folders used when importing, exporting, and reporting. It is important to restrict access to the database connection files.