Migrate the system database with the migration tool using the EXPORT and IMPORT modes

The following step-by-step instructions guide you through the system database migration process when running the migration tool first in EXPORT mode and then in IMPORT mode. Running the tool in EXPORT mode exports the data from the source database as a .db file. Running the tool in IMPORT mode imports the data from the source database .db file into the destination database. The advantage of running the tool in these modes is the generation of the .db file, which can be used for future reference.

Take the following steps to proceed with system database migration.

  1. Block all traffic coming into the current, standing deployment of 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 for EXPORT mode.
    Note: The source database connection properties refer to connection properties associated with the internal system database. Since the values of these properties are internal to the operation of the service, they are read by the migration tool, and should not be specified 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.
    1. Specify the mode: mode = EXPORT.
    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. While the destination properties are not necessary to run the tool in EXPORT mode, they will be used when you subsequently run the tool in IMPORT mode. 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

# 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 that system data was written to the .db file.
    Note: If Migration Tool Execution Status is returned as Success = false, the data was not written to a .db file and the migration tool must be rerun in EXPORT mode.
  10. Configure the migrationTool.properties file for IMPORT: mode = IMPORT.
  11. Navigate to the DBMigrationTool directory and run the shell script. 
    ./DBMigrationTool.sh
  12. Review the log files written to the DBMigrationTool/logs folder to ensure that data from the exported .db file was imported to the destination database.
    Note: If Migration Tool Execution Status is returned as Success = false, the data was not migrated to the external database and the migration tool must be rerun in IMPORT mode.
  13. 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.
  14. Uninstall or archive your provisional installation of the service.
  15. Unblock traffic coming into the standing deployment of the service.