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. The MS SQL build should not be running during migration. But MySQL/PostgreSQL build should be running, so please make sure that it is.
  4. 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

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)
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.
4. Copy the below information from the existing PostgreSQL Applications Manager to MSSQL Applications Manager installation
             <Applications Manager Home>\working\mibs
             <Applications Manager Home>\working\adventnet_ssh_privateKey_file.txt_*
5. 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.