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.

Prerequisites

License

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*

* 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

Source Metadata:

A RED Metadata Repository on SQL Server, Oracle or Teradata which has a RED version of 8.6 or higher
ODBC DSN and user with at least select permissions to the source metadata database.

Data Warehouse:

Any data warehouse platform where the RED 10 version of the WhereScape Target Enablement Pack is available.

Destination Metadata

Destination Metadata:

An empty PostgreSQL database to become your migrated metadata repository.
An ODBC DSN and user with full permissions on the database (see setting up database and users in PostgreSQL for RED).
Compatible PostgreSQL command line tools.

Data Warehouse:

A downloaded and unzipped RED 10 WhereScape Target Enablement Pack for your data warehouse platform.
All prerequisites as outlined in your WhereScape Target Enablement Pack's install guide.
All Data Warehouse Source ODBC Drivers, DSN's and and any required platform specific native tooling are installed and configured (if you are running RED 10 side by side with your Source RED 8.6+ repository then these Drivers, DSN's and tooling should already exist).

Migration Tooling

Migration Tooling Metadata:

An empty PostgreSQL database to house your Migration Tooling, this database can be removed once migration is complete.
An ODBC DSN and user with full permissions on the database (see setting up database and users in PostgreSQL for RED).
Compatible PostgreSQL command line tools.

Tooling:

Downloaded and unzipped Migration Tooling Enablement Pack.
RED 10.4.0.2 or higher installed.
Valid RED license installed with a Custom Target enabled.

How the Migration Tooling Works

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.

General Migration Process

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.

Migrated Object Types

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: Restoring to a version predating the migration would leave your object in an unusable state. The size of the versioning tables in legacy repositories adds unnecessary delay to the migration. It is better to start versioning again from scratch in the migrated repository.
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.

* WS Callable Routines

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.

The Migration Tooling requires the following named connections in RED:

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

Installing the Migration Tool

Check Prerequisites

Check that you have met the prerequisites to begin, here is a quick checklist:

Destination database created on PostgreSQL with a working ODBC DSN configured for it.
Migration Tooling database created on PostgreSQL with a working ODBC DSN configured for it.

Run the RED Setup Wizard

Launch the RED Setup Wizard (RedSetupWizard.exe) from the RED installation directory
Select Create a new repository.
Configure the metadata database - this will be your Migration Tooling metadata.
Select the directory that contains the unzipped RED Migration Tooling. Click Next.
Review the components that will be installed. Click Next.

Create Target Connections

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.

Adding the 'Target' Destination Metadata Connection

The connection named 'Target' will be your PostgreSQL connection to your database to house the migrated RED metadata repository.

On the Connection Name field, ensure to enter 'Target' as a name. On the Target Storage Locations field, ensure to enter 'red'.
Complete the other fields with the appropriate data, then click Validate to check your configurations.
Once you validate your configurations click Add.
On the Add Targets screen you will see the connection you just added. Click Add another target to add the Reports connection.

Adding the 'Reports' Migration Tooling Metadata Connection

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 Connection Name field, ensure you enter 'Reports' as the connection name. On the Target Storage Locations field, enter 'red' as the storage location.
Complete the other fields with the appropriate data, then click Validate to check your configurations.
Once you validate your configurations click Add.
On the Add Targets screen you will see the two connections you just added. Click Next continue and add the source connection.

Create the Source Metadata Connection

On the Add ODBC Sources screen, configure a connection with 'Source' as the connection name, select the DSN relating to your existing RED 8.6 or 9.0 metadata repository. Click Validate to check your connection and then click Add.
The Add ODBC Sources screen will show the connection you just added. Click Next to continue or click Add another source to add more sources.
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.

Enter your login settings and click Connect.

Migration Preparation

Migration Preparation Script

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:

Source Repository Database Type - either SQL Server, Oracle or Teradata
Target Database Enablement Pack - this is the location of the unpacked RED 10 compatible Target Enablement Pack for your licensed target

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

Manual Steps and checks after RED Migration Tooling starts

Check Connections

For each connection Target, Source and Reports:

Open the connection and click the 'Derive' button to ensure the server and port fields are up to date.
Browse the connection to ensure the credentials are working (note that the Target connection will not have any objects to display yet).

Check Parameters

Check the Parameters which the start-up script added make sense for your environment, here is an example below:

Setup the Migration Tooling Azkaban Scheduler on Windows

Windows Scheduler Installation

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.

Configure the Scheduler Credentials in RED

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.

Configure the Scheduler Profile

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.

You will need your Profile Encryption Secret added during Scheduler installation.

Content

Space Tools