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: 9.2 to 10.21
MS SQL: 2008 and above

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

Backup PostgreSQL Data
Configure MS SQL Server
Migrate database

Backup PostgreSQL Data

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

Configure Microsoft SQL Server

Common Settings to be performed in Microsoft SQL Server

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

MS SQL Server in remote computer

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

MS SQL Server Version	Command line Utilities and Native Client
2008	Download
2012	Download
2014	Download
2017	Download
2019	Download
2022	Download

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

Copy the files to <Log360 MSSP 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 → Go to IP Addresses Tab and scroll until TCP Dynamic Ports and enter the current value in your Firewall.

Migrate database

IMPORTANT: Take a Backup/Snapshot of Log360 MSSP before proceeding with the steps

Open the Command Prompt and navigate to <Log360 MSSP home\bin> (Here, Log360 MSSP home is the location where Log360 MSSP is installed).
Stop Log360 MSSP by running shutdown.bat.
Run the ChangeDB.bat.
From the Server Type menu, select the database server you plan to switch to.
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.

Migrate database

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.

Migrate database

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

Migrate database

Note: The user account used must have permission to create a database in the selected MS SQL Server.

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 are changing the database of a fresh installation of Log360 MSSP.

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

Migrate database

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

Migrate database

Previous Topic Next Topic