Migrating Database (From PostgreSQL to MS SQL Database)

PostgreSQL and MS SQL databases are supported in ManageEngine's Access Manager Plus. 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, 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. Sequence of Steps

If you are using Access Manager Plus 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 Access Manager Plus by specifying their own passwords (which are NOT stored in Access Manager Plus), 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 Access Manager Plus 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 Access Manager Plus setup. This will be helpful if the migration is unsuccessful.

PART 1:

1. Shutdown the Access Manager Plus server.
2. Ensure the process postgres is not running.

PART 2:

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

2. Steps to Re-import Personal Passwords

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

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

3. Troubleshooting Tips