Database Update Wizard: Overview

What is the Database Update Wizard?

The Database Update Wizard moves existing ARCHIBUS project data and schema information between project databases. The Database Update Wizard performs the update by transferring out all data from the source project to a series of CSV extract files associated with the database records. It can then read from these extract files into the destination database. There are three steps in the process:

1. Specify the transfer.
2. Specify which tables on which to operate
3. Perform the transfer.

You may need to transfer data between projects to:

• synchronize changes between projects
• integrate data from remote sites
• upgrade to new versions of the ARCHIBUS schema
• make customized changes to your own project
• migrate data between the Sybase, Oracle, or Microsoft SQL Server database platforms

The Database Update Wizard offers two important benefits:

• The wizard can be run multiple times from the same extract files for comparisons or to retry only certain tables .
• You can run the export on one server and the import on another without needing to establish simultaneous database connections – a key point if the servers are located on different computers or at different sites.

The Database Update Wizard also has a comparison option so that you can compare the differences between two projects before actually transferring the data. Comparing follows the same process as transferring:

1. Specify the comparison.
2. Specify which tables on which to operate
3. Perform the comparison.

How Does the Database Update Wizard Work?

The Database Update Wizard is a middle-ware automation tool that implements the same abstract schema definition on different database servers. This allows the ARCHIBUS program and domains to use the same functionality regardless of which database server underlies the program.

The Database Update Wizard can automatically implement database features needed for real estate, infrastructure, and facility management. Because of this, the Database Update Wizard has several advantages over standard data modeling or reverse-engineering tools:

• It understands and transfers not only the SQL table definitions but also those aspects of the ARCHIBUS data dictionary that control editing and reporting. For example, the knowledge of drawing-driven fields such as Room Areas is preserved. The knowledge of bar code fields, such as Equipment Codes, is also preserved allowing bar codes to display in reports.
• It migrates other aspects of your site's schema, such as your lists of asset objects, your layering conventions, and your telecommunications connection hierarchy.
• It handles database server differences in memo field lengths, the significance of trailing blanks, and the differences in how date and time fields are used with different servers.
• It can make best use of the database server's native features to implement required functionality. For instance, auto-numbered tables are implemented using the AUTOINCREMENT default in Sybase, the IDENTITY constraint in Microsoft SQL Server, and triggers and sequences in Oracle.

When migrating changes to your schema, the wizard can replace or merge the changes as desired.

Example: Transfer all Project Data in a Standard Schema

Business process owners, without assistance from a system administrator or database administrator, can use the Database Update Wizard to transfer all project tables in a standard schema from one project to another (with the second database having a superset of the first one’s schema). This is the “standard” upgrade for users that have a standard ARCHIBUS schema.

Example: Upgrade a Project Database with a New ARCHIBUS Schema

In this case, a database administrator (DBA) wants to upgrade the database SQL tables in their project database to match the new ARCHIBUS schema that came with a newer version of ARCHIBUS. In order to move the least amount of data, the standard method is to read the ARCHIBUS schema changes into the project database.

The steps to do so are diagrammed and listed below.

First, you will need a project that has a standard ARCHIBUS schema that reflects the new version of the database. One way to achieve this is to use New Project in the Smart Client to create a new project with the new ARCHIBUS schema. Once you have created this new project, follow this procedure:

1. Sign In. In Web Central, sign in to the project that has this database with the standard schema.
2. Transfer Out. Use the Database Update Wizard to transfer out the Data Dictionary, Application Dictionary, and Process Navigator tables.
3. Sign In. In Web Central, sign in to your project database.
4. Transfer In the Data Dictionary. Use the Database Update Wizard to transfer in just the Data Dictionary – the table and field definitions from which the SQL table structures are built.
5. Upgrade Database. Run the Schema Change Wizard to upgrade the structure of the physical SQL tables to the new format.
6. Transfer In Other Tables. Use the Database Update Wizard to transfer in the Application Dictionary and Process Navigator tables.

See Also

Understanding the ARCHIBUS Database