The RED Migration Tooling allows you to migrate metadata repositories from WhereScape RED 8.6 and 9.0 to WhereScape RED 10.4+. The following sections provide information on how to install and use the RED Migration Tooling.
The RED Migration Tooling requires a 'Custom Target' enabled license. This is because the tooling will use the Custom Target database type for loading into the destination PostgreSQL RED metadata database. For customers on a traditional SQL Server, Oracle or Teradata target your license may need to be temporarily upgraded to support the migration by adding a Custom Target.
| License Fields | Values | Migration Requirements |
|---|---|---|
| Licensed Metadata Database Type(s) | SQL Server, Oracle, Teradata | One or more of SQL Server, Oracle or Teradata |
| Licensed Target Database Type(s) | SQL Server, Oracle, Teradata, Custom | 'Custom' at a minimum |
| Licensed Custom Target Database Type | Any | Any Custom Target Type* |
Licensed Custom Target Database Type is the label in your license given to your Custom Target, this will be used for PostgreSQL targets during migration. This is just a display label for the underlying Custom target type, the important differentiator is that it is not one of the in-built target types SQL Server, Oracle or Teradata and can therefore be used for any other template enabled target platform. |
Source Metadata:
Data Warehouse:
Destination Metadata:
Data Warehouse:
Migration Tooling Metadata:
Tooling:
The RED Migration Tooling is provided as an Enablement Pack which is installed, using the RED Setup Wizard, to a dedicated PostgreSQL database. Once installed you will have a RED metadata repository + the Migration Tooling Enablement Pack which provides a set of scripts and jobs to transfer RED Metadata from a Source of SQL, Oracle or Teradata to a Destination of PostgreSQL and then reconfigure the Destination to suit RED 10 and the Azkaban Scheduler.
The RED Migration Tooling will try to retain wherever possible the existing Scripts and Procedures as is rather than regenerating them in RED 10.
All Objects associated to Script based or Procedure based processing in the Source Metadata Repository will not be regenerated or recompiled in the Destination Metadata, instead it is assumed that the RED 10 Target Enablement Pack will provide a suitable Action Processing Script template that generates appropriate code to deal with the legacy script output protocols and parameters in procedures.
In RED 10 all Scheduling Actions for an Object are performed through an Action Processing Script which is built for and associated to each table, the RED Migration Tooling will generate this script for each object that requires one, this generation process can take minutes to hours depending on the size of the metadata repository, machine resources and database performance.
Not all object types from earlier versions or RED are available in RED 10 so it is important to understand what will and won't be migrated, refer to the following table for more details:
| Object Type(s) | Migrated | Post Migration Notes |
|---|---|---|
| Connections |  | All connections are migrated, MSAS connections should be manually removed after migration. |
| MSAS, Cubes, Cube Dims, Tabular Cubes |  | Analysis Services Object Types are not migrated since RED 10 does not support them yet. |
| Aggregate Dimension Join Table Aggregate Fact Join Table Aggregate Join Table Fact Rollup Fact Work Table Permanent Stage Model View Fact View | | These legacy object sub-types are migrated but assigned an new Custom Object Type in RED 10 of the same name. Objects of these types should be checked carefully in the Destination metadata. |
| All Other Object Types | | All other object types not mentioned in the rows above are migrated as is. |
| Object Versions | | Previous object versions are not migrated. There are a few reasons for this:
|
| WhereScape Callable Procedures* | | Since the inbuilt WhereScape Callable Routines are compiled on either SQL Server, Oracle or Teradata they can not be migrated* |
| Non-Script-Based Loads | | Non-Script-based loads such as: ODBC, DB Link, SSIS and some File Load types are migrated however these load type will require a load script to be generated and therefore these types will need thorough testing post migrations. Any Load which was already script-based should function as is provided the appropriate table level Action Processing Script has been generated. |
| Non-Script-Based Exports | | Non-Script-Based Exports will require an Export script to be generated and therefore these types will need thorough testing post migrations. Any Export which was already script-based should function as is, provided the appropriate Export level Action Processing Script has been generated. |
Any Procedures/Blocks or Scripts which called these callable routines before will continue to work but the outcomes will be applied to the original Source Metadata Repository and depending on the procedure being called will have no effect. Only the WhereScape Parameter Functions will still be of use as is post migration. Most use cases, outside of Parameter read/writes, will involve a customized script or procedure, these should be reviewed to find the RED 10 equivalent and adjusted after migration. Including any Jobs they were part of. Note: Target Enablement Packs will handle legacy procedures that include the WhereScape Parameter read/write functions by synchronizing the dss_parameter table in the Target with the same table in the PostgreSQL metadata repository. In this way most procedures will continue to function as is after migration. |
You should set these up during the initial run of the RED Setup Wizard as outlined in the next section. Listed here for clarity only. |
| Connection Name | Type | Database Type | Target Storage Location | Notes |
|---|---|---|---|---|
| Target | Target | "Any Custom Type" | red | Refers to your Destination RED metadata database on PostgreSQL |
| Reports | Target | "Any Custom Type" | red | Refers to your Migration Tooling metadata database on PostgreSQL |
| Source | Source | SQL Server, Oracle or Teradata | n/a | Refers to your Source Metadata that will be migrated by the tooling |
Check that you have met the prerequisites to begin, here is a quick checklist:
You must create two PostgreSQL connections with the following characteristics:
| Connection Name | Database Type | Target Storage Location | Notes |
|---|---|---|---|
| Target | Custom* | red | Refers to your Destination RED metadata database on PostgreSQL |
| Reports | Custom* | red | Refers to your Migration Tooling metadata database on PostgreSQL |
* Custom will be your licensed Custom Database Target type which might have a different label in the UI than 'Custom', basically for these two connections we can't use the inbuilt SQL, Oracle or Teradata target types. |
The connection named 'Target' will be your PostgreSQL connection to your database to house the migrated RED metadata repository.
The connection named 'Reports' will be your PostgreSQL connection to your Migration Tooling metadata repository, which allows us to add targets to the tooling metadata database for reporting.
On the Summary screen review your configurations are correct, you can click Previous to make changes or click Install to continue.
Once the installation finishes, click Finish to close the installer and launch RED.
Review your login settings and click Connect.
RED Setup Wizard runs with elevated privileges, therefore when RED is launched from the final page it is also starts with the same elevation. If you you start the RED Migration Tooling manually then please run med.exe as Admin as one of the scripts in the Migration Tooling relies on this elevation. |
When WhereScape RED starts for the first time, after the installation steps described in the previous section, the script that prepares the Migration Tooling executes automatically.
If you have not set up the required connections, the Results pane will display a failure message similar to the image shown below. Please expand the Connections node in the left tree and add or amend connections as required before rerunning the script.
If you do add or adjust connections at this point, then ensure you 'Save your Connection Profile' and restart RED so that the in-memory profile of connections credentials is up to date.
The Migration Preparation Script will prompt for two items:
If you get failures in the Reports pane after opening WhereScape RED, then one or more of the preparation steps in the host script named '1_prepare_migration' did not succeed. Take note of the failure message and see if you can correct the issue, then rerun the script. On subsequent script runs you may get additional failures due to the earlier run having already applied a change but in general rerunning this script will not cause issues and some failures when re-run may be dismissed |
For each connection Target, Source and Reports:
Check the Parameters which the start-up script added make sense for your environment, here is an example below:
We'll need a Windows Scheduler installed to perform the migration tasks. Follow the Windows Scheduler Installation instructions to install a WhereScape RED Scheduler for the RED Migration Tooling metadata.
When asked for a Scheduler Metadata database use the RED Migration Tooling metadata database. When asked for a RED Metadata database also use the RED Migration Tooling metadata database. Remember your Profile Encryption Secret for later entry into the Scheduler Profile Maintenance wizard in the RED UI. |
After installing the Scheduler ensure to enter your scheduler credentials into the Configuration page of the Scheduler tab in RED, then Save your Profile again to ensure your credentials are preserved between RED sessions.
Before running any jobs, you must first setup the Scheduler Profile which adds the encrypted connection credentials rows for the connections in RED. This makes those credentials available to scheduled jobs. To do this run the script 'wsl_scheduler_profile_maintenance' found under 'Host Scripts' in the object tree in RED.
Use the same Profile Encryption Secret which you entered during the Scheduler installation. |
Run the migration Jobs one at a time, following the order specified at the beginning of the job name.
The largest job is '2_Migrate_Current_Objects' and takes the longest to complete, this job should be completed successfully before continuing with Job 3. If there are any failures in Job 2, you should view the failure reason and restart the job at the point of failure from the Azkaban Scheduler Dashboard directly, by rerunning the failed execution.
2_Migrate_Current_Objects is intended for SQL and Teradata source repositories, 2_Migrate_Current_Objects_Oracle is intended for Oracle only. Ensure you only one of these jobs depending on your source metadata repository type. |
If there are any failures in Job 3, you can complete the job manually from the RED UI by running the scripts in the order outlined in the Migration Scripts Explained section.
After Job 3 has completed, or you have run the scripts manually, please log in to the migrated Destination repository and allow the RED 10 Target Enablement Pack post install process to complete. This is also a good point to check the connections and save a RED Profile for your migrated Destination metadata repository.
Job 4 applies the default templates which were set up by the RED 10 Target Enablement Pack, this is why it is important to have completed that install process by logging in to the Destination.
Jobs 5, 6 and 7 are optional, each runs a single script that can also be run from the RED UI, see the script details for the scripts prefixed with 'c' in the following section.
To repeat the migration process a second time you do not need to reinstall the Migration Tooling, you can simply drop and recreate the Destination database, run script '2_target_repo_creation', then run the jobs again in the order specified. |
These are the Migration Tooling Scripts, each script can be run from the RED UI or via the indicated Scheduled Job. If you choose to run these scripts manually, please follow the order carefully as listed here.
All the scripts, except for 1 and 2, can be rerun at anytime if required to address failures or if the job 2_Migrate_Current_Objects has been completely rerun.
| Script Name | Purpose | Notes | Job |
|---|---|---|---|
| 1_prepare_migration | Sets up required parameters for the tooling. | Auto-run on startup | n/a |
| 2_target_repo_creation | Creates the RED metadata in the Destination PostgreSQL database. | Auto-run on startup | n/a |
 RUN 1_, and 2_ jobs before the following 'b#_' scripts | These two jobs should be successful before continuing 'b#_' scripts or later jobs. | 1_Source_Reports 2_Migrate_Current_Objects | |
| b1_upgrade_obj_subtypes | Updates the migrated objects subtype keys to be compatible with RED 10. | Run after Migration Job '2_Migrate_Current_Objects' completes. | 3_Prepare_Target_Repository |
| b2_job_metadata_updates | Updates schedules and job states to be compatible with RED 10. | 3_Prepare_Target_Repository | |
| b3_storage_metadata_updates | Creates a new Metadata connection and a Target Location to represent legacy 'local' storage objects then associates those objects to the new target. | 3_Prepare_Target_Repository | |
| b4_reset_identity_sequences | Source metadata keys have been migrated to PostgreSQL as is, so the sequences in PostgreSQL need updating to reflect the migrated data. This script preforms those updates. | 3_Prepare_Target_Repository | |
| b5_target_ep_installation | Uses RedCli to install the RED 10 Target Enablement Pack from the location specified during the initial configuration step to the Destination metadata. | This script requires elevated privilege's since RedCli installs WS modules to the ProgramData directory. If the script fails, then execute it directly from RED after running RED as Administrator. Additionally the RED 10 Target EP must be on a path accessible to the Scheduler Service User if running via the Job. Unpacking the Enablement Pack locally is best. | 3_Prepare_Target_Repository |
| b6_import_sch_integration_scripts | The RED 10 scheduler integration scripts are removed when migrating the script tables, therefore we need to import them again. | 3_Prepare_Target_Repository | |
| b7_apply_legacy_obj_subtypes | OPTIONAL: Creates custom Object Types for legacy object subtypes which don't exist in RED 10, then updates any of those object types to the new custom types. | 3_Prepare_Target_Repository | |
| Log in to the migrated Destination repository to allow the Target Enablement pack to complete it's configurations. | Before running c#_ scripts, log in to the Destination repository and allow the RED 10 Enablement Pack post installation script to complete. | ||
| c1_set_storage_templates | Associates objects with the default storage templates of the target connection they are associated with. This update will not overwrite existing associations and only adds missing templates. | 4_Set_Storage_Templates | |
| c2_generate_windows_action_scripts | RUN EITHER c2 OR c3 OR BOTH: Generates Windows Action Scripts for all objects via RedCli Batch. | long running script, consider running via the scheduled job. You can run either c2 or c3 or both depending on your Scheduler environments. | 5_Generate_Windows_Action_Scripts |
| c3_generate_linux_action_scripts | RUN EITHER c2 OR c3 OR BOTH: Generates Linux Action Scripts for all objects via RedCli Batch. | Long running script, consider running via the scheduled job. You can run either c2 or c3 or both depending on your Scheduler environments. | 6_Generate_Linux_Action_Scripts |
| c4_generate_routines | OPTIONAL: Generates Load/Update routines for all objects. | This is not recommended since it will regenerate all routines using the newer RED 10 templates, instead you should generate routines via the RED UI while logged in to the Destination metadata and only do so for objects that have no associated scripts or procedures. | 7_Generate_Routines |