PostgreSQL/MySQL to MS SQL Migration

Note:
1. The migration is now possible only in a Windows version of Applications Manager. 
2. You need to be on version 13210 of Applications Manager or above to do migration, so please check the build number in the Support tab.

Prerequisites

  1. Take backup of the PostgreSQL/MySQL database before starting migration.
  2. Install the Applications Manager with same build number by selecting MS SQL backend, start & stop it once.
    3. Note:
    1. While migrating Enterprise edition, you should first migrate Managed server and then Admin server.
    2. While installing new instance for migrating Enterprise edition, you should choose Professional edition during installation which will be automatically converted to respective Enterprise edition during migration.
  • The MS SQL build should not be running during migration. But MySQL/PostgreSQL build should be running, so please make sure that it is.
  • Open Migration.properties file in <Applications Manager Home>\conf folder in PostgreSQL/MySQL Applications Manager and enter details of the Microsoft SQL Server like hostname, port, database name, username & password same as you have provided in step 2.
# -------------------------------------
# Target Database Details for migration
# -------------------------------------
am.importdb.type=mssql
am.importdb.host=APP-WIN7-64-1
# we don’t need to mention port if the target server is a SQLServer instance
am.importdb.port=1433
am.importdb.instance=-
am.importdb.name=AMDB_MSSQL
# For Domain authentication should be given as DomainName\\Username
am.importdb.user=sa
am.importdb.password=password
Note: The MSSQL database username defined in Migration.properties file should have bulkadmin permission and permission to access text files in C drive of MSSQL database server.

Steps for Migration

1. In the  PostgreSQL/MySQL Applications Manager execute the below under <Applications Manager Home>\bin\Migration folder in command window (make sure you run as administrator).
       MigrateToMSSQL.bat Export 
     A specific Migration.zip file will be created in <Applications Manager Home>\working\ location.
             AppManagerMigration.zip (Professional Edition)
             AdminServerMigration.zip (Admin Server)
             ManagedServer_X0000000.zip (Managed Server With ServerID X)
Note: Check for any failure logs present under AppManagerHome/logs/Migration during the export process. If any, contact appmanager-support@manageengine.com
 
2. Copy your specific zip file & unzip it under the C:\migration folder of the Windows server where your MSSQL database is running.
3. Now in PostgreSQL Applications Manager, execute the below command under <Applications Manager Home>\bin\Migration folder in command prompt window. 
             MigrateToMSSQL.bat Import
 This will import your data from PostgreSQL/MySQL DB and insert into target DB (Example: AMDB_MSSQL) in the SQL Server.
Note: Check for any failure logs present under AppManagerHome/logs/Migration during the Import process. If any, contact appmanager-support@manageengine.com
 
4. In Postgresql/MySQL AppManager run the \bin\BackupConfig.bat (or sh) script and copy the \working\backup\backupconfzip_Type_Build_Date_HH_MM_SS.zip file to the same location in the MSSQL AppManager.
5. Take a backup of AMServer.properties under AppManager_HOME/conf folder in MSSQL build.
6. In MS SQL build run the \bin\RestoreConfig.bat (or sh) script with the backupconfzip_Type_Build_Date_HH_MM_SS.zip file that was copied in the 4th step.
7. Stop the PostgreSQL/MySQL Applications Manager and start the MS SQL Applications Manager. You will have all your existing monitors/configurations and reports in that with data stored in MS SQL server now.
 
Note:
Currently we have the following options while migrating
a. Can exclude any table without migrating
b. Can specify row limit for tables which have more rows count.
 
(Event or AM_Disk_MinMaxAvgData or similar tables can have million of rows, so we can set a limit if a need arises). These options can be given in migration.conf file under <Applications Manager Home>\working\conf\PGSQL\ for PostgreSQL build and <Applications Manager Home>\working\conf\MySQL\ for MySQL build.
 
Note:
1. This step is not mandatory and may be required while very large database migration only.
2. After migration, it is mandatory to copy and paste all the prerequisites files needed for monitoring from MySQL\PostgreSQL to the MSSQL build.
3. Once database migration is completed, please confirm that there are no failed tables. Once this is done, start the migrated build.