icon-pdfDownload PDF lhs-panel
lhs-panel Click here to expand

Migrating the built-in database server (PostgreSQL) to Microsoft SQL Server or another instance of a PostgreSQL Server.

Supported database migrations

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

Supported database versions

  • PostgreSQL: 10 to 14
  • MS SQL: 2008 and above

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

Backup PostgreSQL Data

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

Configure Microsoft SQL Server

Common Settings to be performed in Microsoft SQL Server

  • Open SQL Server Configuration Manager.
  • Goto SQL Server Services and ensure the service SQL Server Browse is running.
  • 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.
  • 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

  • Go to SQL Server Management Studio.
  • Expand the following <MACHINE_NAME>\SQLEXPRESS → Security → Logins.
  • Check whether the user provided in the Log360 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'

Note: Details about user roles: Refer the documents in the following links: For Server Level Roles: http://msdn.microsoft.com/en-us/library/ms188659.aspx For Database Level Roles: http://msdn.microsoft.com/en-us/library/ms189121.aspx

Server Role of the user should be 'sysadmin' and Database Role of the user should be 'db_owner'. The members of sysadmin server role can perform any activity in SQL Server and have completes control over all database functions. The members of db_owner database role can perform any activity in the database.

MS SQL Server in local computer

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

  • Location of the bcp.exe file: <MSSQL_installed_folder>\Client SDK\ODBC\...\Tools\Binn\bcp.exe. For example, C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\...\Tools\Binn\bcp.exe.
  • Location of the bcp.rll file: <MSSQL_installed_folder>\Client SDK\ODBC\...\Tools\Binn\Resources\1033\bcp.rll. For example, C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\...\Tools\Binn\Resources\1033\bcp.rll

MS SQL Server in remote computer

Note: Please install the corresponding SQL Native Client / Command line Utilities in the Log360 machine as per the MS SQL Server version and CPU type of Log360 machine.

MS SQL Server Version

Native Client

2008

 Download

2012

 Download

2014

 Download

2017

 Download

2019

 Download
Note: MS SQL server version 2022 is also supported by Log360.

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

Copy the files to <Log360 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 for Migration

Note: Take a Backup/Snapshot of Log360 before proceeding with the steps (Important)

  1. Open the Command Prompt and navigate to <Log360 home\bin> (Here, Log360 home is the location where Log360 is installed).
  2. Stop Log360 by running shutdown.bat.
  3. Run the ChangeDB.bat.
  4. From the Server Type menu, select the database server you plan to switch to.
  5. If you select PostgreSQL Server, then:
    • In the Host Name and Port field, enter the host name or IP address and the port number of the PostgreSQL database server.
    • Enter the username and password of a user with the necessary permissions to create a new database.

    Prerequisites for PostgreSQL migration

  6. If you select MS SQL Server, then:
    • Move the bcp.exe and bcp.rll files into the bin folder manually.
    • In the Host Name and Port field, 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 use either 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.

      • Prerequisites for PostgreSQL migration

    • If you want to use Windows authentication, select Windows Authentication, and enter the username and password of a Windows domain user account.

      • Prerequisites for PostgreSQL migration

    • Note: The user account used must have permission to create a database in the selected MS SQL Server.
  7. Check the box next to Migrate Existing Data to copy the data from your old database to the new database.

    8. IMPORTANT: Leave this box unchecked only if you are changing the database of a fresh installation of Log360.

  8. If the MS SQL server you wish to migrate to has Force encryption enabled, check the box next to SSL connection.
  9. Click Test Connection and wait for the connection to be established.

    10. Prerequisites for PostgreSQL migration

  10. Once Test Connection has been established successfully, click Configure DB to initiate migration.

    11. Prerequisites for PostgreSQL migration

Copyright © 2020, ZOHO Corp. All Rights Reserved.

Get download link