Migrating CAST AIP schemas from Oracle Server and Microsoft SQL Server

Note that this tool is no longer maintained and has been removed from CAST AIP ≥ 8.3.15 for the following reasons:

  • the tool does not support transfers to CSS3/PostgreSQL 9.6
  • CSS3/PostgreSQL must be used with CAST AIP ≥ 8.3.x (CSS2/PostgreSQL 9.2 is deprecated for use with 8.3.x)
Summary: this page describes how to migrate CAST AIP schemas installed on Oracle Server and Microsoft SQL Server instances over to CAST Storage Service (CSS) / PostgreSQL.

Introduction

If you have installed your CAST AIP schemas (Analysis/Dashboard/Management Services) on a commercial RDBMS such as Oracle Server or Microsoft SQL Server and you wish to migrate these existing schemas to CAST Storage Service / PostgreSQL then CAST has created a tool (CAST-AIPStorageMigrationTool.exe) specifically to do this for you. The tool will:

  • migrate a triplet of CAST AIP schemas (Analysis/Dashboard/Management Services) and their entire contents to CAST Storage Service / PostgreSQL
  • migrate one single CAST AIP schema and its entire contents to CAST Storage Service / PostgreSQL

You can use the tool in GUI mode, or in CLI mode. Each mode is explained below.

The tool relies on the presence of an XML based configuration file (called configuration.castmigration) which provides the "instructions" about what needs to be migrated and configured the source RDBMS and target CAST Storage Service / PostgreSQL. This needs to be created before using the tool.

Supported CAST AIP releases

The tool supports the migration of any type of CAST AIP schema (Analysis / Dashboard / Management Services) installed on an Oracle Server or Microsoft SQL Server with CAST AIP 7.0.x and above to a CAST Storage Service / PostgreSQL.

Note that:

  • Measurement Services can only be installed on CAST Storage Service / PostgreSQL, therefore they are not part of the scope of the tool.
  • Once a CAST AIP schema has been migrated to CAST Storage Service / PostgreSQL with the tool, you must ensure that you use it in conjunction with the same release of CAST AIP as prior to the migration. In other words, if you want to use the schemas with a new release of CAST AIP, you must follow the standard CAST AIP Upgrade instructions.
  • Migrations to CSS1 are not supported.

Supported CAST Storage Services/PostgreSQL

CSS1 / PostgreSQL 8.4.x(tick)
CSS2 / PostgreSQL 9.2.x(tick)
CSS 3 / PostgreSQL 9.6.x(error)

configuration.castmigration file

Before you can use the tool to migrate your schemas, you need to create a configuration.castmigration XML file which will contain the instructions about what needs to be migrated and information about the source RDBMS and target CSS. An example file is shown below:

<?xml version="1.0" encoding="UTF-8"?>
<Configuration originServerDriverClassName="DRIVER_NAME" originServerUrl="URL" targetServerDriverClassName="org.postgresql.Driver" targetServerUrl="jdbc:postgresql://SERVER_NAME_OR_IP_ADDRESS:<2280|2282>/postgres" licenseKey="LICENSE_KEY">
 <MBSchema name="MNGT_NAME" skipCustomTables="true|false" state="" isSelected="true|false">
  <KBSchema name="LOCAL_NAME" skipCustomTables="true|false" state=""/>
  <CBSchema name="CENTRAL_NAME" skipCustomTables="true|false" state=""/>
 </MBSchema>
</Configuration>

<configuration> tag

This tag contains the values needed to connect to the source RDBMS and the target CSS server, the CAST AIP license key and an optional value for those migration schemas from CAST AIP 7.0.x:

ElementDescriptionOptionsRequired?

originServerDriverClassName

Defines the JDBC driver used to connect to the source RDBMS.(tick)

originServerUrl

Defines the URL (in JDBC format) used to connect to the source RDBMS.
  • Oracle SID: jdbc:oracle:thin:@HOST:PORT:SID
  • Oracle SERVICE: jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
  • Microsoft SQL Server: jdbc:jtds:sqlserver://<server>[:<port>][/<database>]
(tick)
targetServerDriverClassNameDefines the JDBC driver used to connect to the target CSS.org.postgresql.Driver(tick)
targetServerUrlDefines the URL (in JDBC format) used to connect to the target CSS.jdbc:postgresql://HOST:<2280|2282>/postgres(tick)
licenseKeyDefines the CAST AIP license key assigned to the schemas you wish to migrate.Full CAST AIP license key in the format: Sample&amp;Test:1;AIP/EFP:20121031:ABCDEFGH(tick)
Note that you can only specify one source and one target server per configuration.castmigration file.

<MBSchema> tag

This tag defines the CAST AIP schemas that will be migrated from the source RDBMS to the target CSS server:

ElementDescriptionOptionsRequired?
nameDefines the name of the Management Service you would like to migrate.Name of the CAST Management Service.(tick)
skipCustomTablesWhen set to True, any custom tables you may have created in the CAST schemas on the source RDBMS will NOT be migrated to CSS. When set to False or the option is omitted entirely, then all tables are migrated.True | False(error)
state

This option should be left blank. It will be updated with a status on completion of the migration process:

  • Migrated - the schema was successfully migrated
  • Blank or any other entry - the schema was not migrated
N/A(tick)
isSelectedThis option can be set to True (the Management Service and all associated schemas will be migrated) or False (the Management Service and all associated schemas will NOT be migrated).True | False(tick)

Note that:

  • When specifying a Management Service, you must also specify the associated Analysis and Dashboard Services in their own child tags (KBSchema and CBSchema)
  • The associated Analysis and Dashboard Services must be stored on the same source server.

<KBSchema> / <CBSchema> tag

These two tags must be defined as child tags of the <MBSchema> tag:

ElementDescriptionOptionsRequired
nameDefines the names of the Analysis or Dashboard Services you would like to migrate. These services must be associated to the parent Management Service you have defined in the <MBSchema> tag.Name of the CAST Analysis or Dashboard Service.Yes
skipCustomTablesWhen set to True, any custom tables you may have created in the CAST schemas on the source RDBMS will NOT be migrated to CSS. When set to False or the option is omitted entirely, then all tables are migrated.True | FalseNo
state

This option should be left blank. It will be updated with a status on completion of the migration process:

  • Migrated - the schema was successfully migrated
  • Failed - the schema migration failed
  • Skipped - the schema migration was skipped (incorrect version, connection error, schema exists on target already
  • Halted - the schema migration was halted because another related schema migration failed
N/AYes

Examples

Oracle using SID

<?xml version="1.0" encoding="UTF-8"?>
<Configuration originServerDriverClassName="oracle.jdbc.OracleDriver" originServerUrl="jdbc:oracle:thin:@192.168.20.10:1521:ORA" targetServerDriverClassName="org.postgresql.Driver" 
targetServerUrl="jdbc:postgresql://192.168.20.11:2280/postgres" licenseKey="Sample&amp;Test:1;AIP/EFP:20121031:ABCDEFGH">
<MBSchema name="ABC_MNGT" state="" skipCustomTables="true" isSelected="true">
	<KBSchema name="ABC_LOCAL" skipCustomTables="true" state=""/>
	<CBSchema name="ABC_CENTRAL" skipCustomTables="true" state=""/>
</MBSchema>
</Configuration>

Oracle using Service

<?xml version="1.0" encoding="UTF-8"?>
<Configuration originServerDriverClassName="oracle.jdbc.OracleDriver" originServerUrl="jdbc:oracle:thin:@//192.168.20.10:1521/PRODMASSTEST05.castsoftware.com" targetServerDriverClassName="org.postgresql.Driver" targetServerUrl="jdbc:postgresql://192.168.20.11:2280/postgres" licenseKey="Sample&amp;Test:1;AIP/EFP:20121031:ABCDEFGH">
<MBSchema name="ABC_MNGT" state="" skipCustomTables="true" isSelected="true">
	<KBSchema name="ABC_LOCAL" skipCustomTables="true" state=""/>
	<CBSchema name="ABC_CENTRAL" skipCustomTables="true" state=""/>
</MBSchema>
</Configuration>

Microsoft SQL Server

<?xml version="1.0" encoding="UTF-8"?>
<Configuration originServerDriverClassName="net.sourceforge.jtds.jdbc.Driver" originServerUrl="dbc:jtds:sqlserver://192.168.20.15:1433" targetServerDriverClassName="org.postgresql.Driver" 
targetServerUrl="jdbc:postgresql://192.168.20.11:2280/postgres" licenseKey="Sample&amp;Test:1;AIP/EFP:20121031:ABCDEFGH">
<MBSchema name="XYZ_MNGT" skipCustomTables="true" state="" isSelected="true">
	<KBSchema name="XYZ_LOCAL" skipCustomTables="true" state=""/>
	<CBSchema name="SYZ_CENTRAL" skipCustomTables="true" state=""/>
</MBSchema>
</Configuration>

Migrating only one schema

In this example, only the V123_LOCAL schema will be migrated. Adding state="Migrated" to the V123_MNGT and V123_CENTRAL schemas will force only the V123_LOCAL schema to be migrated:


<?xml version="1.0" encoding="UTF-8"?>
<Configuration originServerDriverClassName="oracle.jdbc.OracleDriver" originServerUrl="jdbc:oracle:thin:@//192.168.20.10:1521/PRODMASSTEST05.castsoftware.com" targetServerDriverClassName="org.postgresql.Driver" targetServerUrl="jdbc:postgresql://192.168.20.11:2280/postgres" licenseKey="Sample&amp;Test:1;AIP/EFP:20121031:ABCDEFGH">
 <MBSchema name="V123_MNGT" skipCustomTables="true" state="Migrated" isSelected="true">
  <KBSchema name="V123_LOCAL" skipCustomTables="true" state=""/>
  <CBSchema name="V123_CENTRAL" skipCustomTables="true" state="Migrated"/>
 </MBSchema>
</Configuration>

Note that if you are only migrating either the Analysis or Dashboard Service (KBSchema or CBSchema), you should ensure that you attach this schema to a Management Service (MBSchema/MNGT) in the target CSS server using the CAST Management Studio:

Migrating multiple triplets

It is possible to migrate multiple triplets in one configuration.castmigration file:

<?xml version="1.0" encoding="UTF-8"?>
<Configuration originServerDriverClassName="oracle.jdbc.OracleDriver" originServerUrl="jdbc:oracle:thin:@//192.168.20.10:1521/PRODMASSTEST05.castsoftware.com" targetServerDriverClassName="org.postgresql.Driver" targetServerUrl="jdbc:postgresql://192.168.20.11:2280/postgres" licenseKey="Sample&amp;Test:1;AIP/EFP:20121031:ABCDEFGH">
<MBSchema name="V123_MNGT" skipCustomTables="true" state="" isSelected="true">
	<KBSchema name="V123_LOCAL" skipCustomTables="true" state=""/>
	<CBSchema name="V123_CENTRAL" skipCustomTables="true" state=""/>
</MBSchema>
<MBSchema name="XYZ_MNGT" skipCustomTables="true" state="" isSelected="true">
	<KBSchema name="XYZ_LOCAL" skipCustomTables="true" state=""/>
	<CBSchema name="SYZ_CENTRAL" skipCustomTables="true" state=""/>
	</MBSchema>
</Configuration>

GUI mode

To use the tool in GUI mode, double click the CAST-AIPStorageMigrationTool.exe file that is located at the root of the CAST installation folder:

  • You need to fill in the fields in the Options tab (see explanation below).
  • Then click Run Application to start the migration process.

Options tab

FieldDescription
ConfigurationEnter the path to your configuration.castmigration file stored on the local hard drive. Use the Browse button to find the file.
Origin UserEnter the username that will be used to access the source RDBMS defined in the configuration.castmigration file. CAST recommends that you use a user with DBA equivalent rights. You can also use the same level of rights that is required to install CAST schemas.
Origin PasswordEnter the password for the username that you have entered in the Origin User field.
Target UserEnter the username for your target CSS server defined in the configuration.castmigration file. This should always be Operator.
Target PasswordEnter the password for the Operator username that you have entered in the Target User field. Unless you have changed this, it should be set to CastAIP.
Expected VersionEnter the CAST AIP version number (for example 7.3.4) of the schemas you want to migrate. Any schemas defined in the configuration.castmigration file that do not correspond to this version number will be ignored.
CAST binariesEnter the path to the root folder of your CAST AIP installation, for example: C:\Program Files (x86)\CAST\7.3. The CAST AIP binaries must match the CAST AIP version of the schemas defined in the configuration.castmigration file.

Advanced tab

The Advanced tab is not normally visible and you do not need to modify any options in this tab.

CLI mode

To use the tool in CLI mode, CAST recommends creating a batch file containing the CLI instructions. You must use the CAST-AIPStorageMigrationTool-CLI.exe file that is located at the root of the CAST installation folder. An example batch file is below:

Note that:

  • The instructions should all be placed on one line - we have used carriage returns to make the example more readable
  • Any paths that contain white space must be surrounded by quote marks
CAST-AIPStorageMigrationTool-CLI.exe
-config C:\Users\James\configuration.castmigration
-originuser sa
-originpassword cast
-targetuser operator
-targetpassword CastAIP
-version 8.2.0
-binaries "C:\Program Files (x86)\CAST\8.2.0"
-l c:\temp\migration.log 

Options

OptionDescription
-configEnter the path to your configuration.castmigration file stored on the local hard drive. Use the Browse button to find the file.
-originuserEnter the username that will be used to access the source RDBMS defined in the configuration.castmigration file. CAST recommends that you use a user with DBA equivalent rights. You can also use the same level of rights that is required to install CAST schemas.
-originpasswordEnter the password for the username that you have entered in the Origin User field.
-targetuserEnter the username for your target CSS server defined in the configuration.castmigration file. This should always be Operator.
-targetpasswordEnter the password for the Operator username that you have entered in the Target User field. Unless you have changed this, it should be set to CastAIP.
-versionEnter the CAST AIP version number (for example 8.2.0) of the schemas you want to migrate. Any schemas defined in the configuration.castmigration file that do not correspond to this version number will be ignored.
-binariesEnter the path to the root folder of your CAST AIP installation, for example: C:\Program Files\CAST\8.2. The CAST AIP binaries must match the CAST AIP version of the schemas defined in the configuration.castmigration file.
-lEnter a path to a .log file in which to store the migration log.