Database migration

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

Important points to remember

• Supported database migrations:
• pgSQL Server to Microsoft SQL Server or another instance of pgSQL Server.
• Microsoft SQL Server to pgSQL Server or another instance of Microsoft SQL Server.
• Supported database versions:
• PostgreSQL: 9.6 to 15.8
• Microsoft SQL: 2012 and above
• Take a backup of the database before you proceed.
• It is suggested to apply the Windows service packs and cumulative updates recommended by Microsoft while migrating to Microsoft SQL Server.

Prerequisites

For Microsoft 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

Note In order to ensure that bcp.exe's dependencies are installed correctly, Open Command Prompt in <AD360 installation directory>\bin and run bcp.exe /v. If the bcp.exe file's version is displayed it means the dependencies are properly installed.

• For migration to Microsoft SQL, please install the corresponding SQL Native Client in the AD360 machine as per the Microsoft SQL Server version.
  Note

  Use the links below to download the appropriate SQL command line utilities and ODBC driver based on the SQL Server version and CPU architecture of the machine AD360 is installed. The command line utilities include files with the term SQLCMD.

  For SQL Server 2012, use the SQL Native Client (sqlncli). For other versions, use the ODBC driver.

  Ensure compatibility between the command line utilities and ODBC driver by checking the System Requirements and the compatibility matrix on the respective download pages.

  
  

  SQL Server version	Command line utilities (cmdlnutils)	Native Client (ncli)	ODBC Driver Download Link
  2012	https://www.microsoft.com/en-us/download/details.aspx?id=36433	https://www.microsoft.com/en-us/download/details.aspx?id=56041	https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server
  2014	https://www.microsoft.com/en-us/download/details.aspx?id=36433	Not needed	https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server
  2016	https://www.microsoft.com/en-us/download/details.aspx?id=52680	Not needed	https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server
  2017	https://www.microsoft.com/en-us/download/details.aspx?id=53591	Not needed	https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server
  2019/2022	https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16&tabs=windows	Not needed	https://learn.microsoft.com/en-us/sql/connect/odbc/windows/release-notes-odbc-sql-server-windows?view=sql-server-ver16

• If firewall is enabled in the Microsoft SQL Server machine, the TCP and UDP ports must be opened.
• If the Microsoft SQL server you wish to migrate to has Force encryption enabled, follow the steps mentioned below.
1. Convert your certificate to .cer format.
1. Open IIS Manager.
2. In the middle pane, click Server Certificates.
3. Open the certificate you want to use, and click the Details tab.
4. Click Copy to file.
5. Click Next in the Certificate Export Wizard that appears.
6. On the Export Private Key screen, select No, do not export the private key, and click Next.
7. On the Export File Format screen, select either DER encoded binary X.509 (.CER) or Base-64 encoded X.509 (.CER), and click Next.
8. Enter a name for the file and click Next, and then Finish.
2. Open Command Prompt and navigate to <Installation directory>\jre\bin. Use the command below to associate the certificate with the Java KeyStore.

   keytool -import -v -trustcacerts -alias myserver -file pathofthecert\certname.cer -keystore "..\lib\security\cacerts" -keypass changeit -storepass changeit -noprompt

   where pathofthecert is the location where the certificate has been stored and certname is the certificate name.

   The certificate will be added to your Java KeyStore.

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 navigate 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. From the Select Database Server menu, select the database server that you want to change to.
5. If you select PostgreSQL Server, then:
• In the DB Server Name/IP and Port fields, enter the host name or IP address and the port number of the PostgreSQL database server.
• Enter the Username and Password of a user who has permission to create a new database.
6. If you select Microsoft SQL Server, then:
• In the DB Server Name/IP and Port fields, enter the host name or IP address and the port number of the Microsoft SQL database server.
• In the Select 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 Server in the Authentication field, then enter the Username and Password.
• If you want to use Windows Authentication, select Windows in the Authentication field, then enter the Username and Password of a Windows domain user account.
  Note

  • The user account entered must have permission to create a database in the selected Microsoft SQL Server.
  • The bcp.exe and bcp.rll files must be manually moved to the AD360 bin folder as mentioned in the prerequisites section.
• If the Microsoft SQL server you wish to migrate to has Force encryption on, select the check box against SSL connection.
7. 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.
8. Click Configure DB.