The following step-by-step instructions guide you through the system database migration process when running the migration tool in EXPORT_IMPORT mode. The advantage of running the tool in EXPORT_IMPORT mode is that data is transferred from the source database to the destination database by running the tool a single time. However, when run in this mode, the tool does not generate a .db file for future reference.

Take the following steps to proceed with system database migration.

  1. Block all traffic coming into the service. The service itself must remain running during the migration process.
    Note: By blocking traffic to the service, you are ensuring that no changes to the system database are made while migration is underway. Any such changes would be lost during migration.
  2. Copy, save, and secure the files in the key location specified during the initial installation or previous upgrade of the service. These files must be accessible to the product installation program for a provisional installation of the service.
    Warning: The key location directory contains encryption keys that are used to protect sensitive data in the system database. If the keys are lost, any previously configured data sources would not be usable. Access to the key location directory should be secured with appropriate access controls.
    Note: The key location can be found in the <install_dir>/update.properties file where the key location is the value of the KEY_LOCATION property.
  3. Set up the database that will be used to store Hybrid Data Pipeline user and configuration information. This database must meet the requirements described in System database requirements.
  4. Perform a provisional installation of the service. The product version must be the same as that used in the standing deployment of the service.
    1. Make the installation program file an executable using the chmod +x command. For example:

      chmod +x ./PROGRESS_DATADIRECT_HDP_SERVER_nn_LINUX_64_INSTALL.bin

    2. Run the installation program.
    3. Select the Custom installation type.
    4. When prompted for a key location, specify the location of the files you copied and saved in Step 2.
    5. When prompted for the type of database you want to use, select External Database.
    6. When prompted, select the database which will serve as the new system database.
    7. Provide the required database information such as hostname, port, and database name.
  5. Extract the migration tool from the DBMigrationTool.tar.gz file provided by your Progress representative.
  6. Configure the migrationTool.properties file.
    1. Specify the mode: mode = EXPORT_IMPORT.
    2. Specify the source database connection properties.
      • Option 1. If the source database is the internal database, then you will not need to specify any values in most circumstances. However, if you are running the migration tool from a machine other than the machine hosting the Hybrid Data Pipeline server, the source.db.hostname connection property must specify the host machine.
      • Option 2. If the source database is an Oracle database, then you must specify values for each property. The database administrator credentials should be used for the source.db.username and source.db.password properties.
    3. Specify the destination database connection properties. The connection properties of the external system database created during the provisional installation of the server in Step 4 must be specified. The database administrator credentials should be used for the destination.db.username and destination.db.password properties.
    4. Optionally, specify tables you wish to exclude from the migration.
    5. Optionally, specify a data folder for persisting the exported data. (By default, data is persisted in a data folder in the current directory.)
    6. Optionally, specify the number of times you want the tool to retry a failed operation. (The default is 2.)
    7. Optionally, specify the gap between retry operations. (The default is 3000 ms.)

    The following sample provides additional details.

    Note: The acronym HSQLDB refers to the internal system database.
    # Applicable values IMPORT,EXPORT,EXPORT_IMPORT
# Defaults to 'EXPORT' mode, if omitted
mode = EXPORT_IMPORT

# Source Database Connection Properties - Required if mode is EXPORT,
   EXPORT_IMPORT
# Defaults to HDP's default HSQLDB properties, if omitted

# Defaults to localhost
source.db.hostname = 
source.db.port = 
source.db.username = 
source.db.password = 
# Applicable values HSQLDB, ORACLE
# Defaults to HSQLDB
source.db.type =
#Database name property is not applicable for Oracle Database type
source.db.name =
source.db.schemaName =
# Extended Options can be configured in the format: key1=value1;key2=value2
   For oracle this can be used to specify SID=<ORACLE SID>, serviceName=
   <ORACLE SERVICE NAME>
source.db.extendedOptions = 

# Destination Database Connection Properties - Required if  mode is IMPORT,
   EXPORT_IMPORT
destination.db.hostname = mysystemdbhost.abccorp.com
destination.db.port = 1433
destination.db.username = SystemDBAdmin  
destination.db.password = Secret
# Applicable values MSSQL,POSTGRESQL
destination.db.type = MSSQL
#Database name property is not applicable for Oracle Database type
destination.db.name = HDPSystemDB
destination.db.schemaName = hdp_db
# Extended Options can be configured in the format: key1=value1;key2=value2
   For oracle this can be used to specify SID=<ORACLE SID>, serviceName=
   <ORACLE SERVICE NAME>
destination.db.extendedOptions =

# Tables which could be excluded from migration
excludedTables =
   DATASTORE,LIMITS,CLOUDDB_CONFIG,CLOUDDB_FILE,CLOUDDB_LOG,CLOUDDB_MAP,
   CLOUDDB_NATIVE,CLOUDDB_SCRIPT,CSCACHE,CLOUDDB_PROPERTIES,PASSWORDPOLICY,
   SWAGGERDOCUMENT,PERMISSIONS,AUTHENTICATIONTYPE

# The folder path to persist the exported data
# Defaults to 'data' folder in the current directory
data.folder.path =

# Number of times to retry the operation in case of exception while Migrating.
# Defaults to 2
retryLimit =

# Time gap Between Retry operations in milliseconds.
# Defaults to '3000' ms.
retryDelay =
  7. Grant execute permission to the migration tool shell script DBMigrationTool.sh.
  8. Navigate to the DBMigrationTool directory and run the shell script. 
    ./DBMigrationTool.sh
  9. Review the log files written to the DBMigrationTool/logs folder to ensure the successful migration of system data from the internal database to the external database.
    Note: If Migration Tool Execution Status is returned as Success = false, the migration was unsuccessful and the migration tool must be rerun.

    The following sample log file shows the operations attempted and the success or failure of the data migration.

    2018-11-13 09:59:00 INFO HDPMigrationTool:loadConfig():150 - Reading
migrationTool.properties from :C:\Users\test\
Workspace\DBMigrationTool\config\migrationTool.properties
2018-11-13 09:59:00 INFO HDPMigrationTool:run():52 - Running in mode: EXPORT_IMPORT
2018-11-13 09:59:00 INFO HDPMigrationTool:runImport():152 - Connecting to
   Destination DB ..
2018-11-13 09:59:01 INFO HDPMigrationTool:runImport():154 - Connection to
   Destination DB
      Successful.
2018-11-13 09:59:01 INFO HDPMigrationTool:read():417 - Import Process started ...
2018-11-13 09:59:05 INFO HDPMigrationTool:importFromFile():401 - Importing :
   tableName:Organization,success:true, SourceRowCount:2, destRowCount:2, ms:1929       
2018-11-13 10:00:10 INFO HDPMigrationTool:importFromFile():401 - Importing :
   tableName:CloudDB_Script,success:true, SourceRowCount:0,destRowCount:0, ms:688        
2018-11-13 10:00:20 INFO HDPMigrationTool:read():445 - Total Import time in ms:78768     
2018-11-13 10:00:20 INFO HDPMigrationTool:logToolStatus():119 -

** Migration Tool Execution Status **
Success = true
************
  10. Perform an upgrade on the standing deployment of the service. The upgrade enables you to use the external database to support your standing deployment of the service. The product version used to upgrade the standing service must be the same as that used in the installation or subsequent upgrade of the service.
    1. Run the installation program for the version of the product you are using.
    2. When prompted, enter the installation directory of your standing deployment of the service. Click Next, and then click Upgrade to continue.
    3. Select Modify as the type of upgrade you want to perform.
    4. When prompted for the type of database you want to use, select External Database.
    5. When prompted, select the database which will serve as the new system database.
    6. Provide the required database information such as hostname, port, and database name.
    7. If you are upgrading a load balancer deployment, perform an Express upgrade on the additional nodes running the service.
  11. Uninstall or archive your provisional installation of the service.
  12. Unblock traffic coming into the standing deployment of the service.