Migrating the Built-in database server (PostgreSQL) to MS SQL Server or another instance of a PostgreSQL Server

Supported database migrations

• PostgreSQL Server to MS SQL Server or another instance of PostgreSQL Server.
• MS SQL Server to PostgreSQL Server or another instance of MS SQL Server.

Supported database versions

• PostgreSQL: 9.6 to 10.21
• MS SQL: 2008 and above

Note: It is suggested to apply the Windows service packs and cumulative updates recommended by Microsoft while migrating to MS SQL Server.

To migrate the built-in PostgreSQL to a different database, follow the steps listed below.

• Backing up PostgreSQL Data
• Configuring MS SQL Server
• Steps to migrate database

Backing up PostgreSQL Data

1. Stop the AD360 Server/Service.
2. Invoke the <AD360 Home>\bin\backupDB.bat in command prompt to backup the data available in PostgreSQL database. By default, the backup file will be stored under <AD360 Home>\Backup\AD360_Backup<Backup_time> directory.

Configuring MS SQL Server

Common Settings to be performed in MS SQL Server

1. Open SQL Server Configuration Manager.
2. Goto SQL Server Services and ensure the service SQL Server Browse' is running.
3. Goto SQL Server Network Configuration → Protocols for SQLEXPRESS (the given instance while configuring the MS SQL) → Enable TCP/IP. Then restart the SQL Server (SQLEXPRESS - the given instance) Service.
4. Set the following configuration for the SQL Server Configuration Manager:
   • SQL Server Network Configuration → Protocols for <instances> → Enable everything.
   • SQL Native Client Configuration → Client Protocols → Enable all.

Providing credentials to other users in the domain

1. Go to SQL Server Management Studio.
2. Expand the following <MACHINE_NAME>\SQLEXPRESS → Security → Logins.
3. Check whether the user provided in the AD360 Service is already in the list.

If not, right click the Logins, New Login and provide a corresponding user name. The New user must have the sysadmin server level role and database level role of db_owner.

Follow the steps to provide the sysadmin role permission:

Right click the user, click 'Properties'
Go to 'Server Roles' → Check sysadmin and click 'OK'

MS SQL Server in local computer

Copy the following files to <AD360 Home>\bin folder.

• bcp.exe- <MSSQL_installed_folder>\Client SDK\ODBC\130\Tools\Binn\bcp.exe
• bcp.rll- <MSSQL_installed_folder>\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.

MS SQL Server in remote computer

Note: The links provided below will redirect you to the main Microsoft SQL feature pack page. On pressing the corresponding download button, you get a set of command line utilities and native client formats. Install the corresponding SQL Native Client or command line utilities as per the MS SQL Server version and CPU type of the machine where AD360 is installed. The command line utilities have the term SQLCMD in them, and the native client file can be found under the name sqlncli.

After installing the Command Line utilities, please copy the following files:

Copy the files to <AD360 Home>\bin folder.

• bcp.exe- <MSSQL_installed_folder>\Client SDK\ODBC\130\Tools\Binn\bcp.exe
• bcp.rll- <MSSQL_installed_folder>\Client SDK\ODBC\130\Tools\Binn\Resources\1033\bcp.rll

Windows Firewall Settings

If the Firewall is enabled in MS SQL Server machine, the TCP and UDP Ports need to be opened.

UDP Port is normally 1434.

To check TCP Port settings, open SQL Configuration Manager:

• SQL Server Network Configuration → Protocols for <instances>
• Right click TCP/IP → Properties → Goto IP Addresses Tab and scroll until TCP Dynamic Ports and enter the current value in your Firewall.

Steps to migrate 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 MS 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 MS 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 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.
   • If the MS 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.