best Migrating Data between different Databases

Migrating from PostgreSQL Database to MS SQL Database

ManageEngine PAM360 supports the following databases: PostgreSQL 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 from the PostgreSQL database to the MS SQL databases.

1. Steps Required

If you are right now using PAM360 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.

Important Prerequisites:

1. 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  these steps under MS SQL Configuration before you begin the migration.

2. Before commencing the migration, ensure the following aspects are taken care of:

i. Personal Password Management: If users in your organization have used the "Personal Password Management" option in PAM360 by specifying their own passwords (which are NOT stored in PAM360), 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.

ii. Bundled Database Only: The migration can be carried out only if your current PAM360 installation uses the database bundled with the product (PostgreSQL). This procedure DOES NOT apply if you are using any other external database.

iii. Before initiating the migration, take a complete back up of the existing PAM360 setup. This will be helpful if the migration is unsuccessful.

PART 1:

1. Shutdown the PAM360 server.
2. Ensure the process "postgres" is not running.

PART 2 :

  1. Navigate to <PAM360 Installation Folder>/bin folder and execute the file MigrateDB.bat (Windows) or sh MigrateDB.sh (Linux). In the window that pops up, choose the option "Go to Migration Set Up".

  1. 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 PAM360. If the instance name is not specified, PAM360 will try establishing connection with the default instance on port 1433.
      Since PAM360 connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for PAM360. 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 PAM360 database. Default is "PassTrix". If you want to have a different database name, you may specify here. PAM360 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 PAM360 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 PAM360 connects to the database. [The username and password entered here will be stored in the "database_params.conf" file in PAM360. 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 PAM360 to generate a key, else if you want to have your custom key, select the option "Custom".
    7. If you have selected the option "Custom:" 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 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 PAM360 server.

Troubleshooting Tips:

Error Cause Effect Solution

Server seems to be running. Shutdown PAM360 server and try again.

You might have attempted the database migration when the PAM360 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 "<PAM360-Installation-Folder>/bin" folder and then try again. If the issue persists, contact PAM360 support with the ".lock" file.



Important Note:

After completing the migration, start the PAM360 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.

Steps to Re-import Personal Passwords

In general, PAM360 does not allow the migration of personal passwords. Hence, before data migration, personal passwords need to be exported out of PAM360 in the form of an XLS file. Once the migration process is over, the passwords can be re-imported into PAM360 using a CSV or TXT file. Here are the steps:

  • Open the .xlsx file, containing the personal passwords, using MSExcel.
  • Click "File >> Save as" and choose either of the formats .csv or .txt.
  • Click Save.

       

©2019, ZOHO Corp. All Rights Reserved.

Top