Migrating OpManager Database

For OpManager version 125230:

PGSQL to MSSQL Migration Steps :

  1. In the PGSQL setup, go to OpManager home → bin, start Command Prompt with administrator privilege from this path and run DBConfiguration.bat.
  2. In the popup shown, please chose MSSQL, check "Migrate data from the existing database" option and click OK. 
  3. After the migration is complete, start the product and check if it is working properly

MSSQL to PGSQL Migration (For Prepopulated and non prepopulated setups):

  1. In the MSSQL setup, make the below changes in db_migration.conf in the <OpManagerHome>\conf directory.
  2. Change the value of dest.db.postgres.dir to <OpManagerHome>/pgsql directory (E.g., dest.db.postgres.dir = <OpManager Base Home>/pgsql). 

    This step is not applicable for OpManager versions 125324 and above.
  3. Note: It is mandatory to use "/" as a directory separator.

  4. Now go to OpManager home → bin, start Command Prompt with administrator privilege from this path and run DBConfiguration.bat.
  5. In the popup shown, please chose PostgreSQL and check "Migrate data from the existing database" option and click OK.
  6. After the migration is complete, start the product and check if it is working properly.

MSSQL to PGSQL Migration (Remote PGSQL)

  1. In the MSSQL setup, make the below changes in db_migration.conf in <OpManager Base Home>\conf directory.
    • create.dest.db=false
    • start.dest.postgres.server=false
  2. Rename the database_params_dbconfig.conf.bkp file in <OpManagerHome>\conf\OpManager\POSTGRESQL folder if it exists.
  3. Create a database in Remote PostgreSQL server.
  4. Migrating OpManager Database

  5. Create 'rouser' for read-only permissions. Connect to pgAdmin and execute the below queries:
    • CREATE USER rouser with password '<ROPASSWORD>';
    • CREATE EXTENSION IF NOT EXISTS pgadmin SCHEMA pg_catalog;
    • REVOKE ALL ON SCHEMA public FROM rouser, public;
    • GRANT CONNECT ON DATABASE "<DatabaseName>" TO rouser, public;
    • GRANT USAGE ON SCHEMA public TO rouser, public;
    • GRANT SELECT ON ALL TABLES IN SCHEMA public TO rouser, public;
    • ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT SELECT ON TABLES TO rouser, public;
    • GRANT USAGE ON SCHEMA public TO rouser;
    • GRANT SELECT ON ALL TABLES IN SCHEMA public TO rouser;
    • ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO rouser;
  6. Migrating OpManager Database

  7. Changes to be made in the <OpManagerHome>\conf\OpManager\POSTGRESQL\database_params.conf
    • Make the changes in URL field jdbc:postgresql://<remotePgSQLInstalledIPAddress>:<PortNumber>/<DBName>?dontTrackOpenResources=true&useUnicode=true&characterEncoding=utf8
    • Change the username to postgres. (username=postgres)
    • Encrypt the password of the postgres user and change the same in the above mentioned. file (password=<EncryptedPassword>)
    • Add the property isBundledPgSQL=false as well in the same file.
    • Change the ro_password to the password supplied in point 4 <ROPassword>. Encrypt the same and include it in the same file (ro_password=<ROPassword>)
    • Encryption of the plain text can be done using <OpManagerHome>\bin\encrypt.bat by specifying the algorithm as AES.256 and by using CryptTag which can be found in <OpManagerHome>\conf\customer-config.xml (Usage:: encrypt.bat -v <Password> -a AES.256 -k <CryptTag> )
  8. Migrating OpManager Database

  9. Now go to OpManager home → bin, start Command Prompt with administrator privilege from this path and run DBConfiguration.bat.
  10. In the popup shown, please chose PostgreSQL and check the "Migrate data from the existing database" option and click OK.
  11. After the migration is complete, start the product and check if it is working properly.

DB MIGRATION

Migrating SQL server from one machine to another

Note: These steps are applicable for all versions of OpManager.

  1. Stop OpManager Service.
  2. Take a SQL DB backup using the SQL Management tool.
  3. Restore the MSSQL DB backup(taken from old server) in the new server using the SQL Management tool.
  4. Open cmd with admin privelege and go to <OPMHome>/bin and run DBConfiguration.bat. Provide new sql details and save.
  5. Start OpManager Service.

For OpManager version 12.5

Note: The following steps are only applicable for Windows installations. The supported PgSQL versions are 10.12 and above.

Migrating from PgSQL to MSSQL

  1. Download and install the latest version of OpManager (choose MSSQL DB while installing).
  2. Do not hit 'Finish' at the end of installation.
  3. In the old PGSQL setup, go to <OpManagerHome>\bin>, start Command Prompt from that path and run the 'MigrateDB.bat'file with the required parameters.

    MigrateDB.bat mssql <opm_home_from_new_MSSQL_setup>/conf/databaseParams.conf

  4. After migration, navigate to <OpManagerHome>\conf\OpManager in the old PgSQL setup, copy the data-dictionary.xml file and replace it under the same directory in the new MSSQL setup.
  5. Start the OpManager service and check if it works properly.

Migrating from MSSQL to PgSQL (Pre-populated setup)

  1. Download and install the latest version of OpManager (choose PGSQL DB while installing).
  2. Do not hit 'Finish' at the end of installation.
  3. Provide a new DBNAME for database_params.conf in the new PGSQL setup.
  4. In the old MSSQL setup, make the below changes in the db_migration.conf file under<OpManager Base Home>\conf:

    create.dest.db=true
    start.dest.postgres.server=true

  5. In the db_migration.conf file (in the old MSSQL setup) under<OpManager Base Home>\conf, change the value of dest.db.postgres.dir
  6. dest.db.postgres.dir = <OpManager New PgSQL Home>/pgsql

    Note: It is mandatory to use "/" as the directory separator

  7. In the old MSSQL setup, go to <OpManagerHome>\bin, start Command Prompt with administrative privilege from the same path and run the 'MigrateDB.bat' file with the required parameters.
  8. MigrateDB.bat postgres <OpManager New PgSQL Home>\conf\database_params.conf

  9. After migration, navigate to <OpManagerHome>\conf\OpManager in the old MSSQL setup, copy the data-dictionary.xml file and replace it under the same directory in the new PgSQL setup
  10. Now, start OpManager Service from the new PGSQL setup and check.

Migrating from MSSQL to PgSQL (Non Pre-populated setup - All 32 bits and EE setups)

  1. Download and install latest(same) version of OpManager (Choose PGSQL DB while installing).
  2. Do not hit 'Finish' at the end of installation.
  3. Make the below changes in the db_migration.conf file (in the old MSSQL setup) under<OpManager Base Home>\conf:

    create.dest.db=true
    start.dest.postgres.server=true

  4. In the db_migration.conf file (in the old MSSQL setup) under<OpManager Base Home>\conf, change the value of dest.db.postgres.dir
  5. dest.db.postgres.dir = <OpManager New PgSQL Home>/pgsql

    Note: It is mandatory to use "/" as the directory separator

  6. In the old MSSQL setup, go to <OpManagerHome>\bin, start Command Prompt with administrative privilege from the same path and run the 'MigrateDB.bat' file with the required parameters.
  7. MigrateDB.bat postgres <OpManager New PgSQL Home>\conf\database_params.conf

  8. After migration, navigate to <OpManagerHome>\conf\OpManager in the old MSSQL setup, copy the data-dictionary.xml file and replace it under the same directory in the new PgSQL setup.
  9. Now, start OpManager Service from the new PGSQL setup and check.

Migrating from MSSQL to Remote PgSQL

  1. Download and install latest version of OpManager (Choose PGSQL DB while installing).
  2. Do not hit 'Finish' at the end of installation.
  3. Create a new database in the Remote PgSQL server. Provide the new DBNAME and its remote server details in the database_params.conf file of the new PGSQL setup.
  4. Change the value of 'isBundledPgSQL' in the database_params.conf filrto false.
  5. In the db_migration.conf file (in the old MSSQL setup) under<OpManager Base Home>\conf, change the value of dest.db.postgres.dir
  6. dest.db.postgres.dir = <OpManager New PgSQL Home>/pgsql

    Note: It is mandatory to use "/" as the directory separator

  7. In the old MSSQL setup, go to <OpManagerHome>\bin, start Command Prompt with administrative privilege from the same path and run the 'MigrateDB.bat' file with the required parameters
  8. MigrateDB.bat postgres <OpManager New PgSQL Home>\conf\database_params.conf

  9. After migration, navigate to <OpManagerHome>\conf\OpManager in the old MSSQL setup, copy the data-dictionary.xml file and replace it under the same directory in the new PGSQL setup.
  10. Now start OpManager Service from new PGSQL setup.

For OpManager version 12.4 

Migrating from PGSQL TO MSSQL

  • Download the latest version of OpManager (.exe/.bin)
  • After the installation is complete, do not hit the 'Finish' button.
  • In the old PGSQL setup, go to OpManager home → bin, start Command Prompt from this path and run the 'MigrateDB.bat' file with the required parameters.

    MigrateDB.bat mssql <opm_home_from_new_MSSQL_setup>/conf/databaseParams.conf

    (Pointing the newly created MSSQL database from the old PGSQL database)
  • After migration is complete, copy the data-dictionary.xml file from the 'pgsql' folder of the old installation to the 'mssql' folder of the new installation.
  • Start the product, and check if it's working properly.

Migrating from MSSQL TO PGSQL

  • Download the latest version of OpManager (.exe/.bin)
  • After the installation is complete, do not hit the 'Finish' button.
  • In the old MSSQL setup, go to OpManager home directory → pgsql → bin, start Command Prompt from this path and run the following command:

    psql.exe -U postgres -p <postgre_port_number> -h 127.0.0.1

    This establishes a connection between OpManager and the PGSQL server.
  • Next, invoke the create database command with a DB name of your choice.

    create database <New_database_name>;

  • Now, copy the name of the newly created database, and replace it in the databaseParams.conf file in the 'conf' directory.
  • Go to the OpManager home directory of the old PGSQL setup, and run the 'MigrateDB.bat' file with the required parameters.

    MigrateDB.bat postgres <opm_home_from_new_PGSQL_setup>/conf/databaseParams.conf

    (Pointing the newly created PGSQL database from the old MSSQL database)
  • After migration is complete, copy the data-dictionary.xml file from the 'mssql' folder of the old installation to the 'pgsql' folder of the new installation.
  • Start the product, and check if it's working properly.

For builds earlier than 11600, follow the steps below to migrate from MySQL to PGSQL/MSSQL.

Migrating from MySQL to PGSQL

  1. Stop OpManager.
  2. Take a backup: cmd > OpManager\bin\backup
  3. Execute BackupDB.bat -targetdb pgsql (proceed to next step after the backup is completed).    
  4. Since PGSQL was not bundled with earlier versions, take a complete OpManager backup and save it on different location as folder backup.
  5. Uninstall OpManager completely and delete OpManager folder.
  6. Install same build of OpManager with PGSQL database option and make sure OpManager works fine. (link to download older builds of OpManager:  http://archives.manageengine.com/opmanager/)
  7. Stop OpManager service and copy the backup folder located under OpManager from the backup folder to the newly installed folder under the same location.
  8. Restore the database using RestoreDB.bat present under OpManager/bin/backup directory and restart OpManager.

    For ex : C:\<OpManager Home>\bin\backup>RestoreDB.bat "c:\OpManager\backup\BackUp_APR3_2009_17_43_38_8100.zip"

    Note : For linux - please use BackupDB.sh 

Migrating from MySQL to MSSQL

  1. Take a backup: cmd > OpManager\bin\backup
  2. Execute BackupDB.bat -targetdb mssql (proceed to next step after the backup is completed)
  3. Select Start > Programs > ManageEngine OpManager > DB Manager > DB Configuration
  4. A DB Configuration window pops up. Select MSSQL and click on Save.
    Configure the following information:
    1. DB Host : The name or the IP address of the machine where MSSQL is installed.
    2. Port: The port number in which OpManager must connect with the database. Default is 1433.
    3. User Name and Password: The user name and password with which OpManager needs to connect to the database.
    4. Driver Jars: Specify the path of the Database driver
    5. Click OK.
  5. Restore the data using RestoreDB.bat present under OpManager/bin/backup directory and restart OpManager.

          For ex : C:\<OpManager Home>\bin\backup>RestoreDB.bat "c:\OpManager\backup\BackUp_APR3_2009_17_43_38_8100.zip"

Migrating OpManager Server when using MSSQL or Remote PgSQL

  1. Stop OpManager service.
  2. Copy the entire OpManager folder from old Server and move it to required location in the new server.
  3. Go to the new server's command prompt using the 'run as administrator' option.
  4. Change the directory to <OpManagerHome>/bin and execute "opmanagerservice.bat -i"
    Ex: C:\ManageEngine\OpManager\bin>OpManagerService.bat -i
    (The above command will install OpManager service)
  5. Now you can go to Windows Services (Go to run > type Services.msc) and you can find "ManageEngine OpManager" service listed which should be used to Start and Stop OpManager.

Note: The above steps will work if you have OpManager with MSSQL as the OpManagerDB or Remote PGSQL on a remote server, provided the new OpManager server has access to the remote server database.