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:
- PostgreSQL: 9.6 to 10.21
- MS SQL: 2008 and above
- Take a backup of the database before you proceed.
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.
- 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
- Open Command Prompt and change the directory to <AD360 home\bin> where AD360 home is the location where the instance of AD360 is installed.
- Stop AD360 by running shutdown.bat.
- Run ChangeDB.bat.
- In the wizard that pops up, select AD360.
- From the Select Database Server menu, select the database server that you want to change to.
- If you select PostgreSQL Server, then:
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 pgSQL database server.
- Enter the Username and Password of a user who has permission to create a new database.
Check the box next to Migrate Existing Data to copy the data from your old database to the new database.
- 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.
- 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.
IMPORTANT: Leave this box unchecked only if you want to change the database of a fresh installation of AD360 or its components.