Migration Instructions

NOTE

The process for patching and initial setup is the same. It is important to re-create your transform database from the original 6.7.1 backup and re-run the scripts, as changes will be made to these for patches. Note that using a different backup will not migrate any new or changed data in that version.

Each segment of code in the upgrade and migration scripts executes in a run-once block, with the execution history for migrations stored in the MigrationHistory table in the database. Running the migration script multiple times will only execute new blocks of code that were added in subsequent patch fixes and not harm existing data. Please verify that data exists in MigrationHistory before doing a patch as a precautionary measure.

It is not necessary to create a new 7.x database for patches, as this database already exists.

Sql Server

Three databases are needed to complete the migration process: a 6.7.1 production backup, a “transformation” database for intermediate data manipulation and storage, and a BrM 7 database.

6.7.1 Production Backup

NOTE: This database is not used in any way by the BrM 7.1 application. It is used for comparison purposes and future data patches. Also, agencies may wish to do data cleanup and run multiple migrations as test scenarios while transitioning to BrM 7.1. Ultimately this database may be deleted after BrM 7.1 setup is complete. However, it is recommended to keep the database active or at minimum keep a backup of the database available for future patching.
Create or restore a backup copy of your production BrM 6.x database. If the database is not on BrM version 6.7.1, it must be upgraded to BrM 6.7.1 to complete the migration.
Restore the backup BrM 6.7.1 database to the same server where the BrM 7 database will run.

Transform Database

NOTE: This database is used only during the migration process. It may be deleted after the migration is complete. It must be deleted to run future migrations. If an agency wishes to test data cleanup or data modification scripts, this is the recommended place to do that, due to its transient nature. Ultimately, this database will feed the actual BrM 7 database, but it is not used by the BrM 7 application. Migration scripts WILL modify the data in this database.
The transformation database should simply be a copy of the 6.7.1 database above. The name used for this database will be used in subsequent steps below.
Give the database a logical name, such as MYDOT_TRANSFORM.

7.1 Database (Skip if already on version 7.x of BrM)

Set up a new blank database for BrM 7, if it does not already exist. The initial size required should be similar to the size of the current production database.
The name used for this database will be used in the subsequent steps below. This is the database that will be used by the BrM 7 application.
Make sure to create a login and a database user based on that login. The login must have “db_owner” rights.

Run the Following Scripts

0.sqlserver.datavalidation.script.sql (OPTIONAL)
1. NOTE: This script provides some example data validations to aid in the migration process. (For a complete list, please see individual data concerns in the “Data Migrations” section below.)
2. The script may be run before and/or after the migration process. To run the script before migrating data, please create the BrM 7 data structures first by running the 2.upgrade.sqlserver703.sql script to ensure tables exist. Some of the output in the script depends on the migration being run first. These outputs will be denoted as “PRE MIGRATION” or “POST MIGRATION”, respectively. Post-migration checks depend on certain data being loaded in BrM 7.1, such as the EventLibrary table.
3. For example, a pre-migration load rating events check validates that BRIDGE_GD is not null for load rating events and warns that NULL records will not migrate. This allows the users to clean data before running the migration process.
4. To run the script simply do a find-and-replace on the @DATABASE_SOURCE /DATABASE_SOURCE and @DATABASE_TARGET/DATABASE_TARGET variables replacing them with your transform database name and BrM 7 database name.
5. NOTE: The migration process will run regardless of any informational or warning messages output by the validation script. Any errors during the migration process will not necessarily be related to the output of the validation script.
1.sqlserver.transformation.scipt.sql
1. Set a value for the @Trim_Option variable on line 67. (Defaults to zero) Please note that this option only applies to the STRUCT_NUM field, not all database fields.
2. Execute the script against the transform database created in the “Transform Database” step above.
2.upgrade.sqlserver703.sql
1. Execute the 2.upgrade.sqlserver703.sql script against the BrM 7 database created in the “7.1 Database” step above. This script creates the tables and columns for the BrM 7 database that will be used by the BrM 7 application. This script ensure that the database has been updated to the latest patch version of BrM 7.x, which is version BrM 7.0.3.
3.sqlserver703.migration.script
1. Set a value for the DATABASE_SOURCE variable on line 84 by replacing “$(Transform7DatabaseName) “with the name of the transform database created above.
2. Set a value for the DATABASE_TARGET variable on line 86 by replacing “$(Brm7DatabaseName)” with the name of the BrM 7 database created above.
3. Set a value for the AGENCY_PREFIX variable on line 88 by replacing 'SUBSTRING(@DATABASE_SOURCE, 1, 2)’ with the 2 or 3 character prefix you would like to use for your user columns prefix.
4. Execute the 3.sqlserver703.migrations.script against the BrM 7 database created above. Test that the data migrated correctly by selecting all from the Bridge table in the BrM 7 database. Additionally, it is recommended to check the output for errors in the migration. This script ensures that the database migration patches up to version BrM 7.0.3 have been executed.
4.upgrade.sqlserver71.sql
1. Execute the 4.upgrade.sqlserver71.sql script against the BrM 7 database created in the “7.1 Database” step above. This script creates the tables and columns for the BrM 7.1 database that will be used by the BrM 7.1 application. This script ensure that the database has been updated to the latest version of BrM, 7.1.
5.sqlserver71.migration.script
1. Set a value for the DATABASE_SOURCE variable on line 84 by replacing $(Transform7DatabaseName) with the name of the transform database created above.
2. Set a value for the DATABASE_TARGET variable on line 86 by replacing $(Brm7DatabaseName) with the name of the BrM 7 database created above.
3. Set a value for the AGENCY_PREFIX variable on line 88 by replacing 'SUBSTRING(@DATABASE_SOURCE, 1, 2)’ with the 2 or 3 character prefix you would like to use for your user columns prefix.
4. Execute the 5.sqlserver71.migrations.script against the BrM 7 database created above. Test that the data migrated correctly by selecting all from the Bridge table in the BrM 7 database. Additionally, it is recommended to check the output for errors in the migration. This script ensures that the database migration patches up to version 7.1 have been executed.

Oracle

The first step of the migration process involves configuration and setup of the database environment. 3 users will be needed to complete the migration: a 6.7.1 production backup, a “transformation” user/schema for intermediate data manipulation and storage, and a BrM 7.1 schema.

6.7.1 Production Backup

NOTE: This schema is not used in any way by the BrM 7 application. It is used for comparison purposes and future data patches. Also, agencies may wish to do data cleanup and run multiple migrations as test scenarios while transitioning to BrM 7. Ultimately this schema may be deleted after BrM 7 setup is complete. However, it is recommended to at least keep a backup of the schema available for future patching. Migration scripts will NOT modify data in this schema.
Export a dump of the production BrM 6.7.1 schema if it does not already exist from previous migrations. If the production schema is not on BrM version 6.7.1, it must be upgraded to BrM version 6.7.1 to complete the migration.
Restore the BrM 6.7.1 dump to the same server where the BrM 7.1 schema will exist. Ensure that there is an appropriate tablespace available for the restoration of the production dump.

Transform Schema

NOTE: This schema is used only during the migration process. It may be deleted after the migration is complete. It must be deleted to run future migrations. If an agency wishes to test data cleanup or data modification scripts, this is the recommended place to do that, due to its transient nature. Ultimately, this schema will feed the actual BrM 7 schema, but it is not used by the BrM 7 application. Migration scripts WILL modify the data in this schema.
The transform schema should simply be a copy of the BrM 6.7.1 production backup. The name used for this schema will be used in subsequent steps below.
1. One possible method to create a duplicate schema is to set up a new tablespace and schema and then import the backup from step 2(b). However, any method may be used. See ora1.sql script for example code.

7.1 Schema (Skip if already on version 7.x of BrM)

Set up a default tablespace for the BrM 7 schema. The initial size will be similar to the size of the current production schema; however, we recommend leaving additional space for growth and new features, perhaps 20-50%.
Create a new blank schema for BrM 7. The name used for this schema will be used in subsequent steps below. This is the schema that will be used by the application.

Run the following scripts. It is recommended to run the scripts in sqlplus and output the results to a text file.
Windows Command Example: sqlplus -s
username/password@DB_CONNECTION_STRING
@/path/to/your/sql/file/example.sql > /path/to/output/file/output.txt

0.oracle.datavalidation.script.sql
1. NOTE: This script provides some example data validations to aid in the migration process. (For a complete list, please see individual data concerns in the “Data Migrations” section below.)
2. The script may be run before and/or after the migration process. To run the script before migrating data, please create the BrM 7 data structures first by running the 2.upgrade.sqlserver703.sql script to ensure tables exist. Some of the output in the script depends on the migration being run first. These outputs will be denoted as “PRE MIGRATION” or “POST MIGRATION”, respectively. Post-migration checks depend on certain data being loaded in BrM 7.1, such as the EventLibrary table.
3. For example, a pre-migration load rating events check validates that BRIDGE_GD is not null for load rating events and warns that NULL records will not migrate. This allows the users to clean data before running the migration process.
4. To run the script simply set the value of the DATABASE_SOURCE and \DATABASE_TARGET variables at the top of the script, replacing them with your transform schema name and BrM 7 schema name.
5. NOTE: The migration process will run regardless of any informational or warning messages output by the validation script. Any errors during the migration process will not necessarily be related to the output of the validation script.
1.oracle.transformations.script.sql
1. The value of TRIM_OPTION on line 108 defaults to a value of 1 but may be modified to trim whitespace during the migration.
2. Execute the script against the transform schema created in “Transform Schema” step above.
2.upgrade.oracle703.sql
1. Execute the 2.upgrade.oracle703.sql script against the BrM 7 schema created in the “7.1 Schema” step above. This script creates the tables and columns for the BrM 7 schema. This script ensure that the database has been updated to the latest patch version of BrM 7, which is version BrM 7.0.3.
3.oracle703.migrations.script
1. Set a value for the DATABASE_SOURCE variable on line 109 by replacing ‘SET SOURCE HERE’ with the name of the transform schema created above.
2. Set a value for the DATABASE_TARGET variable on line 110 by replacing ‘SET TARGET HERE’ with the name of the 7.1 schema created above.
3. Do a find-and-replace-all on the text AGENCY_PREFIX := SUBSTR(DATABASE_SOURCE, 1, 2); replacing it with AGENCY_PREFIX := 'USR'; - changing ‘USR” to your preferred 2 or 3 character user column prefix.
4. Execute the 3.oracle703.migrations.sql script against the BrM 7 schema created above. Test that data migrated successfully by selecting all from the Bridge table in the BrM 7 schema. Additionally, it is recommended to check the output for errors in the data migration. This script ensures that the database migration patches up to version BrM 7.0.3 have been executed.
4.upgrade.oracle71.sql
1. Execute the 4.upgrade.oracle1.sql script against the BrM 7 schema created in the “7.1 Schema” step above. This script creates the tables and columns for the BrM 7.1 schema. This script ensure that the database has been updated to the latest version of BrM, 7.1.
5.oracle71.migrations.script
1. Set a value for the DATABASE_SOURCE variable on line 109 by replacing ‘SET SOURCE HERE’ with the name of the transform schema created above.
2. Set a value for the DATABASE_TARGET variable on line 110 by replacing ‘SET TARGET HERE’ with the name of the 7.1 schema created above.
3. Do a find-and-replace-all on the text AGENCY_PREFIX := SUBSTR(DATABASE_SOURCE, 1, 2); replacing it with AGENCY_PREFIX := 'USR'; - changing ‘USR” to your preferred 2 or 3 character user column prefix.
4. Execute the 5.oracle71.migrations.sql script against the BrM 7 schema created above. Test that data migrated successfully by selecting all from the Bridge table in the BrM 7 schema. Additionally, it is recommended to check the output for errors in the data migration. This script ensures that the database migration patches up to version 7.1 have been executed.

Migration Instructions