Database migration

Using this option, you can change the built-in database server (PostgreSQL) of AD360 to an MS SQL Server or another instance of a PostgreSQL Server (pgSQL).

Important points to remember

• Supported database migrations:
• pgSQL Server to MS SQL Server or another instance of pgSQL Server.
• MS SQL Server to pgSQL Server or another instance of MS SQL Server.
• Supported database versions:
• pgSQL: 9.6 to 10.21
• MS SQL: 2008 and above
• Take a backup of the database before you proceed.

Prerequisites

For MS SQL

• Copy the bcp.exe and bcp.rll files from the directory where the SQL Server is installed and paste them in the AD360 bin folder (<AD360_installed_directory/bin).
• Location of the bcp.exe file: <MSSQL_installed_folder>\Client SDK\ODBC\130\Tools\Binn\bcp.exe. For example, C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe.
• Location of the bcp.rll file: <MSSQL_installed_folder>\Client SDK\ODBC\130\Tools\Binn\Resources\1033\bcp.rll. For example, C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\Resources\1033\bcp.rll
• For migration to MS SQL, please install the corresponding SQL Native Client in the AD360 machine as per the MS SQL Server version.
  

  MS SQL Server Version	Native Client
  2008	Download
  2008 R2	Download
  2012	Download
  2014	Download
  2016	Download
  2017	Download
  2019	Download

• If firewall is enabled in the MS SQL Server machine, the TCP and UDP ports must be opened.

For External pgSQL

• In the machine where pgSQL is installed, go to <postgresql_installdir>/data and open the posgresql.conf file. Search for wal_level entry. Uncomment the entry and change its value to archive.
• Copy all the files in <postgresql_installdir>/lib and <postgresql_installdir>/bin folders and paste them in <product_home>/pgsql/lib and <product_home>/pgsql/bin folders respectively. <product_home> refers to the home directory of AD360 or the integrated products for which you're configuring the auto backup scheduler.
• Restart the external pgSQL server.
• Repeat the steps 1 to 3 from above whenever you update the pgSQL server.

Steps to migrate a database

1. Open Command Prompt and change the directory to <AD360 home\bin> where AD360 home is the location where the instance of AD360 is installed.
2. Stop AD360 by running shutdown.bat.
3. Run ChangeDB.bat.
4. In the wizard that pops up, select AD360.
5. From the Select Database Server menu, select the database server that you want to change to.
6. If you select PostgreSQL Server, then:
• In the DB Server Name/IP and Port field, enter the host name or IP address and the port number of the pgSQL database server.
• Enter the Username and Password of a user who has permission to create a new database.
7. If you select MS SQL Server, then:
• In the DB Server Name/IP and Port field, enter the host name or IP address and the port number of the MS SQL database server.
• In the DB Server Instance field, select the SQL Server instance you want to use.
• For Authentication, you can either use Windows credentials or a SQL Server user account.
• If you want to use a SQL Server user account, then select SQL Authentication and enter the Username and Password.
• If you want to use Windows Authentication, select Windows Authentication, and enter the username and password of a Windows domain user account. To autofill the username and password of the user currently logged in to the machine, check the box next to Use Default Windows Authentication.
  Note:

  • The user account used must have permission to create a database in the selected MS SQL Server.
  • The bcp.exe and bcp.rll files must be manually moved to the AD360 bin folder as mentioned in the prerequisites section.
8. Check the box next to Migrate Existing Data to copy the data from your old database to the new database.
   IMPORTANT: Leave this box unchecked only if you want to change the database of a fresh installation of AD360 or its components.
9. Click Configure.