Database migration

Using this option you can change the built-in database server (PostgreSQL) of EventLog Analyzer to MS SQL Server or another instance of a PostgreSQL Server.

Important points to remember

• 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: 10 to 14
  • MS SQL: 2008 and above
• Take a backup of the database before you proceed.
• We recommend applying the Windows service packs and cumulative updates suggested by Microsoft during your migration to MS SQL Server.
• MySQL to Postgres migration:
  • If you need an external backup for Postgres during the migration from MySQL to Postgres, please contact our support team for assistance.
• Before making changes to the database with the changeDBServer.bat file, it's crucial to ensure that the product is fully operational. Once confirmed, proceed to shut down the product. Once the product has successfully stopped, run the changeDBServer.bat file.

Prerequisites for MSSQL migration

• Copy the bcp.exe and bcp.rll files from the installed SQL server directory and paste them in the EventLog Analyzer bin folder (<EventLog Analyzer_installed_directory/bin).
  • 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
• For migration to MS SQL, please install the corresponding SQL Native Client in the EventLog Analyzer machine as per the MS SQL server version.

SQL Server version	Command Line Utilities (cmdlnutils)	Files to be download for CmdLnUtils	Native Client (ncli)	File to be download for Native client	ODBC Driver (odbc)	File to be downloaded for ODBC Driver
2008	https://www.microsoft.com/en-in/download/details.aspx?id=44272	SqlCmdLnUtils.msi	https://www.microsoft.com/en-in/download/details.aspx?id=44272	sqlncli.msi	Not Needed	Not Needed
2012	64 bit: http://go.microsoft.com/fwlink/?LinkID=239650&clcid=0x409 32 bit: http://go.microsoft.com/fwlink/?LinkID=239649&clcid=0x409 Ref: https://www.microsoft.com/en-us/download/details.aspx?id=29065	The previous column contains the download link.	https://www.microsoft.com/en-in/download/details.aspx?id=50402	sqlncli.msi	Not Needed	Not Needed
2014	https://www.microsoft.com/en-US/download/details.aspx?id=53164	MsSqlCmdLnUtils.msi	Not Needed	Not Needed	https://www.microsoft.com/en-in/download/details.aspx?id=36434	msodbcsql.msi
2016	https://www.microsoft.com/en-us/download/details.aspx?id=56833	MsSqlCmdLnUtils.msi	Not Needed	Not Needed	https://www.microsoft.com/en-us/download/details.aspx?id=56833	msodbcsql.msi
2017	https://www.microsoft.com/en-us/download/details.aspx?id=56833	MsSqlCmdLnUtils.msi	Not Needed	Not Needed	https://www.microsoft.com/en-us/download/details.aspx?id=56833	msodbcsql.msi
2017	https://www.microsoft.com/en-us/download/details.aspx?id=53591	MsSqlCmdLnUtils.msi	Not Needed	Not Needed	https://www.microsoft.com/en-us/download/details.aspx?id=53339	msodbcsql.msi
2019	64 bit: https://go.microsoft.com/fwlink/?linkid=2230791 32 bit: https://go.microsoft.com/fwlink/?linkid=2231320	MsSqlCmdLnUtils.msi	Not Needed	Not Needed	64 bit: https://go.microsoft.com/fwlink/?linkid=2223304 32 bit: https://go.microsoft.com/fwlink/?linkid=2223303	msodbcsql.msi
2022	https://www.microsoft.com/en-us/download/details.aspx?id=53591	MsSqlCmdLnUtils.msi	Not Needed	Not Needed	64 bit: https://go.microsoft.com/fwlink/?linkid=2249006 32 bit: https://go.microsoft.com/fwlink/?linkid=2249005	msodbcsql.msi

Note - MS SQL server version 2022 is also supported by EventLog Analyzer.
• If firewall is enabled in the MS SQL Server machine, the TCP and UDP ports must be opened.
• If the MS SQL server you wish to migrate to has Force encryption enabled, follow the steps mentioned below.
  • Convert your certificate to .cer format.
    • Open IIS Manager.
    • In the middle pane, click Server Certificates.
    • Open the certificate you want to use, and click the Details tab.
    • Click Copy to file.
    • Click Next in the Certificate Export Wizard that appears.
    • On the Export Private Key screen, select No, do not export the private key, and click Next.
    • On the Export File Format screen, select either DER encoded binary X.509 (.CER) or Base-64 encoded X.509 (.CER), and click Next.
    • Enter a name for the file and click Next, and then Finish.
  • 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.

Prerequisites for PostgreSQL migration

1. Open the remote machine where the product is installed & navigate to Product Home\ pgsql\ data\ pg_hba.conf
2. Open pg_hba.conf file and add an entry of the host IP address and its subnet mask as 0.0.0.0/0

3. Navigate to Product Home\ pgsql\ data \ postgresql.conf
4. Open postgresql.conf and change the Listen_addresses as '*' & remove the # in the start of the line.

Database backup for External PostgreSQL

• In the machine where PostgreSQL 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. Create a folder named <product_home>/pgsql/data. <product_home> refers to the home directory of EventLog Analyzer or the integrated products for which you're configuring the auto backup scheduler.
• Restart the external PostgreSQL server.

Repeat the steps 1 to 3 from above whenever you update the PostgreSQL server.

Steps for Migration

1. Open the Command Prompt and navigate to <EventLog Analyzer home\bin> (Here, EventLog Analyzer home is the location where EventLog Analyzer is installed).
2. Stop EventLog Analyzer by running shutdown.bat.
3. Run the tools/ChangeDBServer.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.
   
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.
   
7. If you want to use Windows authentication, select Windows Authentication, and enter the username and password of a Windows domain user account.

Note: The user account used must have permission to create a database in the selected MS SQL Server.
8. 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 EventLog Analyzer.
9. If the MS SQL server you wish to migrate to has Force encryption enabled, check the box next to SSL connection.
10. Click Test Connection and wait for the connection to be established.
11. Once Test Connection has been established successfully, click Configure DB to initiate migration.
12. Click the OK button to initiate the migration process. During the migration, you have the option to cancel if necessary.