Migrating Data between different Databases

ManageEngine Password Manager Pro supports the following databases: PostgreSQL, MySQL and MS SQL, where PostgreSQL comes bundled with the product, by default. In general, there are various reasons to migrate from one database to another which include performance issues, stability, reliability, High Availability issues, costs for the license (dual license, GPL), etc.

This document walks you through the process of migrating your data between the PostgreSQL, MySQL and MS SQL databases. Click the below links for the steps to migrate from one database to another:

1. Migrating data from MySQL Database to PostgreSQL Database
(Procedure applicable only for builds 6801 and later)

Important Prerequisites:

1. Please take a backup of the entire Password Manager Pro directory, prior to migrating the database.
2. The users, who use the Personal tab to store their personal passwords and use their own personal passphrase to log in to the "Personal" tab, must export their passwords out of Password Manager Pro before migration. They can reimport those personal passwords after the migration.
3. Login to Password Manager Pro UI with an administrator account and navigate to Admin >> Audit. Check the total number of resource/user/task audits. If the total number of the resource/user/task audits is > 200000, please contact our support at passwordmanagerpro-support@manageengine.com.

Steps Required:

Note: Please take a complete backup of Password Manager Pro, prior to making any changes, or take a VM snapshot, so that you can restore the data in case of a failure.

Stop the Password Manager Pro service and make sure the mysqld.exe process is not running.
Open a command prompt and navigate to the <Password Manager Pro Installation Folder>/bin directory.
Execute MigrateMySQLToPgSQL.bat (in Windows) or MigrateMySQLToPgSQL.sh (in Linux).
Start the Password Manager Pro service. Now, Password Manager Pro will run with PostgreSQL as the backend database.

2. Migrating Data from MySQL Database to the MS SQL Database
(Procedure applicable only for Password Manager Pro builds 6401 and later)

If you are right now using Password Manager Pro with MySQL database and wish to use the MS SQL database instead, follow the steps below to migrate the data from the former to the latter.

Prerequisites:

This section guides you through only the migration process and does not include any database configuration aspects. Hence, if you have decided to use MS SQL as the dedicated backend database, make sure you have already completed the steps 1 to 3 under MS SQL Configuration before you begin the migration.
Before commencing the migration, ensure the following aspects are taken care of:

Personal Password Management: If users in your organization have used the Personal Password Management option in Password Manager Pro by specifying their own passwords (which are NOT stored in Password Manager Pro), those personal passwords will not be migrated as a part of data migration. You need to use the Export Passwords option in the Personal Passwords section for the same. Learn More.
Bundled Database Only: The migration can be carried out only if your current Password Manager Pro installation uses the database bundled with the product (PostgreSQL). This procedure DOES NOT apply if you are using any other external database.
Before initiating the migration, take a complete back up of the existing Password Manager Pro setup. This will be helpful if the migration is unsuccessful.

If you have Password Manager Pro installed in a Linux environment, do the steps that follow to migrate data from MySQL Database to the MS SQL Database:

Install the respective XRDP based on your Linux operating system.
Post the XRDP installation, take a remote session to the Linux environment using the native Remote Desktop Connection or any other third-party application from a Windows or Windows Domain resource.
Enter the username and password of your Linux environment. Ensure that you use the Xvnc module as the session type.

Now you can manage your Linux environment graphically to execute the below operations for migration.

Steps to Migrate:

PART 1:

1. Shutdown the Password Manager Pro server.
2. Ensure the process mysqld is not running.

PART 2 :

Navigate to the <Password Manager Pro Installation Folder>/bin folder and execute the file MigrateDB.bat (Windows) or sh MigrateDB.sh (Linux). In the window that pops up, choose to read the Best Practices Guide first and then choose the option Go to Migration Set Up.

In the window displayed, enter the following details:
1. Host Name: The name or the IP address of the machine where MS SQL server is installed.
2. Instance Name: The named instance of SQL server used by Password Manager Pro. If the instance name is not specified, Password Manager Pro will try establishing connection with the default instance on port 1433.
  Since Password Manager Pro connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for Password Manager Pro. If you want to specify a port number other than 1433, you can specify it in the Host Name parameter above as <hostname>:<port>.
3. Database Name: Name of the Password Manager Pro database. Default is "PassTrix". If you want to have a different database name, you may specify here. Password Manager Pro will take care of creating the Master Key, Symmetric Key etc.
4. Authentication: The way to connect to the SQL server. If you are connecting to the SQL server from Windows, you have the option to make use of the Windows Single-Sign-On facility, provided the Password Manager Pro service is running with a service account, which has the privilege to connect to the SQL server. In this case, choose the option "Windows". Otherwise, select the option "SQL". [It is recommended to choose the option 'Windows' as the username and password used for authentication are not stored anywhere].
5. User Name and Password: If you have selected the option "SQL", specify the user name and password with which Password Manager Pro connects to the database. [The username and password entered here will be stored in the "database_params.conf" file in Password Manager Pro. So, you need to take care of hardening the host].
  You can also use your Windows login credentials, if you are connecting to the database from Windows. In this case, enter the username as <domain-name>\<username>.
6. Encryption Key: The key with which your data is to be encrypted and stored in the SQL server. You may either leave it Default, allowing Password Manager Pro to generate a key, else if you want to have your custom key, select the option Custom.
7. If you have chosen the option 'Custom', you need to create a new database, create Master Key, create Certificate (this will be certificate name) and Create the Symmetric Key using AES 256 encryption. Follow the below steps:
  
  Create Database >> For details, refer to http://msdn.microsoft.com/en-us/library/aa258257(v=sql.80).aspx
  Create Master Key >> For details, refer to http://technet.microsoft.com/en-us/library/ms174382.aspx
  Create Certificate >> For details, refer to http://msdn.microsoft.com/en-us/library/ms187798.aspx
  Create Symmetric Key >> For details, refer to http://msdn.microsoft.com/en-us/library/ms188357.aspx
  After executing the above steps, you need to provide a certificate name and symmetric key name in the GUI.

PART 3:

Finally, click Test to ensure that the connection settings are proper and then click Migrate. The status of data migration will be displayed in the textbox. Once the migration is complete, start the Password Manager Pro server.

Troubleshooting Tips:

Error	Cause	Effect	Solution
Server seems to be running. Shutdown Password Manager Pro server and try again.	You might have attempted the database migration when the Password Manager Pro server is running,	You will encounter this error in the DB Migration GUI and the GUI will remain open.	Shut down the server and try again. If the error repeats, delete the ".lock" file under the "<Password Manager Pro-Installation-Folder>/bin" folder and then try again. If the issue persists, contact passwordmanagerpro-support@manageengine.com with the ".lock" file.

Important Note:

After completing the migration, start the Password Manager Pro server and navigate to Admin >> Resource Additional Fields and Admin >> Accounts Additional Fields. Open the GUI and click Save. This will restore the additional fields added by you in the migrated instance as well.

3. Migrating Data from PostgreSQL Database to the MS SQL Database
(Procedure applicable only for Password Manager Pro builds 6401 and later)

If you are right now using Password Manager Pro with PostgreSQL database and wish to use the MS SQL database instead, follow the steps below to migrate the data from the former to the latter.

Prerequisites:

This section guides you through only the migration process and does not include any database configuration aspects. Hence, if you have decided to use MS SQL as the dedicated backend database, make sure you have already completed the steps 1 to 3 under MS SQL Configuration before you begin the migration.
Before commencing the migration, ensure the following aspects are taken care of:
1. Personal Password Management: If users in your organization have used the Personal Password Management option in Password Manager Pro by specifying their own passwords (which are NOT stored in Password Manager Pro), those personal passwords will not be migrated as a part of data migration. You need to use the Export Passwords option in the Personal Passwords section for the same. Learn More.
2. Bundled Database Only: The migration can be carried out only if your current Password Manager Pro installation uses the database bundled with the product (PostgreSQL). This procedure DOES NOT apply if you are using any other external database.
3. Before initiating the migration, take a complete back up of the existing Password Manager Pro setup. This will be helpful if the migration is unsuccessful.
If you have Password Manager Pro installed in a Linux environment, do the steps that follow to migrate data from PostgreSQL to MS SQL database:
1. Install the respective XRDP based on your Linux operating system.
2. Post the XRDP installation, take a remote session to the Linux environment using the native Remote Desktop Connection or any other third-party application from a Windows or Windows Domain resource.
3. Enter the username and password of your Linux environment. Ensure that you use the Xvnc module as the session type.
  Now you can manage your Linux environment graphically to execute the below operations for migration.

Steps to Migrate:

PART 1:

1. Shutdown the Password Manager Pro server.
2. Ensure the process postgres is not running.

PART 2 :

Navigate to the <Password Manager Pro Installation Folder>/bin folder and execute the file MigrateDB.bat (Windows) or sh MigrateDB.sh (Linux).
In the pop-up that opens, read the Best Practices Guide first by clicking the Open the best practices document button. Click the Continue with migration setup button to proceed with the migration process.

In the window displayed, enter the following details:
1. Host Name: The name or the IP address of the machine where MS SQL server is installed.
2. Instance Name: The named instance of SQL server used by Password Manager Pro. If the instance name is not specified, Password Manager Pro will try establishing connection with the default instance on port 1433.
  Since Password Manager Pro connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for Password Manager Pro. If you want to specify a port number other than 1433, you can specify it in the Host Name parameter above as <hostname>:<port>.
3. Database Name: Name of the Password Manager Pro database. Default is "PassTrix". If you want to have a different database name, you may specify here. Password Manager Pro will take care of creating the Master Key, Symmetric Key etc.
4. Authentication: The way to connect to the SQL server. If you are connecting to the SQL server from Windows, you have the option to make use of the Windows Single-Sign-On facility, provided the Password Manager Pro service is running with a service account, which has the privilege to connect to the SQL server. In this case, choose the option Windows. Otherwise, select the option SQL. (It is recommended to choose the option Windows as the username and password used for authentication are not stored anywhere).
5. User Name and Password: If you have selected the option SQL, specify the user name and password with which Password Manager Pro connects to the database. [The username and password entered here will be stored in the "database_params.conf" file in Password Manager Pro. So, you need to take care of hardening the host].
  You can also use your Windows login credentials, if you are connecting to the database from Windows. In this case, enter the username as <domain-name>\<username>.
6. Encryption Key: The key with which your data is to be encrypted and stored in the SQL server. You may either leave it Default, allowing Password Manager Pro to generate a key, else if you want to have your custom key, select the option Custom.
7. If you have chosen the option 'Custom', you need to create a new database, create Master Key, create Certificate (this will be certificate name) and Create the Symmetric Key using AES 256 encryption. Follow the below steps:
  
  Create Database >> For details, refer to http://msdn.microsoft.com/en-us/library/aa258257(v=sql.80).aspx
  Create Master Key >> For details, refer to http://technet.microsoft.com/en-us/library/ms174382.aspx
  Create Certificate >> For details, refer to http://msdn.microsoft.com/en-us/library/ms187798.aspx
  Create Symmetric Key >> For details, refer to http://msdn.microsoft.com/en-us/library/ms188357.aspx
  After executing the above steps, you need to provide a certificate name and symmetric key name in the GUI.
8. Instance Type: Password Manager Pro allows both Local Instance (on-premise) and Azure SQL as backend database. Select either one of them to continue with the DB change configuration.
9. Connection Properties: After selecting this checkbox, you will have the option to specify custom connection properties in the following format: "key1=value1;key2=value2;".

Note: The existing server details in the DB migration setup window and properties, such as SSL and encrypt, cannot be added as custom connection properties.

PART 3:

Troubleshooting Tips:

Error	Cause	Effect	Solution
Server seems to be running. Shutdown Password Manager Pro server and try again.	You might have attempted the database migration when the Password Manager Pro server is running,	You will encounter this error in the DB Migration GUI and the GUI will remain open.	Shut down the server and try again. If the error repeats, delete the ".lock" file under the "<Password Manager Pro-Installation-Folder>/bin" folder and then try again. If the issue persists, contact passwordmanagerpro-support@manageengine.com with the ".lock" file.

Important Note:

After completing the migration, start the Password Manager Pro server and navigate to Admin >> Resource Additional Fields and Admin >> Accounts Additional Fields. Open the GUI and click Save. This will restore the additional fields added by you in the migrated instance as well.

Re-importing Personal Passwords

In general, Password Manager Pro does not allow the migration of personal passwords. Hence, before data migration, personal passwords need to be exported out of Password Manager Pro in the form of an XLS file. Once the migration process is over, the passwords can be re-imported into Password Manager Pro.

Migrating Data between different Databases

1. Migrating data from MySQL Database to PostgreSQL Database (Procedure applicable only for builds 6801 and later)

Steps Required:

2. Migrating Data from MySQL Database to the MS SQL Database (Procedure applicable only for Password Manager Pro builds 6401 and later)

Prerequisites:

Steps to Migrate:

PART 1:

PART 2 :

PART 3:

Troubleshooting Tips:

3. Migrating Data from PostgreSQL Database to the MS SQL Database (Procedure applicable only for Password Manager Pro builds 6401 and later)

Prerequisites:

Steps to Migrate:

PART 1:

PART 2 :

PART 3:

Troubleshooting Tips:

Re-importing Personal Passwords

1. Migrating data from MySQL Database to PostgreSQL Database
(Procedure applicable only for builds 6801 and later)

2. Migrating Data from MySQL Database to the MS SQL Database
(Procedure applicable only for Password Manager Pro builds 6401 and later)

3. Migrating Data from PostgreSQL Database to the MS SQL Database
(Procedure applicable only for Password Manager Pro builds 6401 and later)